October 7, 2015 at 8:16 am
Hello everyone,
I have this script that captures metrics and sizes using T-SQL.
When running in development I have no issues.
I can run under the context of any database and get results back.
When deploying to production servers no results are returning.
I'm trying to figure out what permissions are needed for this to return results.
No error message is being returned either.
Any suggestions on resolving this issue. May access is limited for triage against Production?
I need to be able to have Production DBA set the right permissions so this job can run correctly.
DECLARE @allocation_table table
(
dbname sysname,
reservedpages bigint,
usedpages bigint,
pages bigint
)
INSERT INTO @allocation_table
EXEC sp_MSforeachdb N'IF EXISTS
(
SELECT 1 FROM SYS.DATABASES WHERE name = ''?'' AND NAME NOT IN(''master'',''msdb'',''model'',''tempdb'') and STATE=0
--customize to monitor specific databases
--SELECT 1 FROM SYS.DATABASES WHERE name = ''?'' AND NAME IN(''EMPLOYEE'') and STATE=0
)
BEGIN
SELECT
''?'',
SUM(a.total_pages) as reservedpages,
SUM(a.used_pages) as usedpages,
SUM(
CASE
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
) as pages
from ?.sys.partitions p join ?.sys.allocation_units a on p.partition_id = a.container_id
left join ?.sys.internal_tables it on p.object_id = it.object_id
END';
SELECT
-- from first result set of 'exec sp_spacedused'
@@ServerName as ServerName,
db_name(sf.database_id) as [database_name]
,ltrim(str((convert (dec (15,2),sf.dbsize) + convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as [database_size]
,ltrim(str((case when sf.dbsize >= pages.reservedpages then
(convert (dec (15,2),sf.dbsize) - convert (dec (15,2),pages.reservedpages))
* 8192 / 1048576 else 0 end),15,2) + ' MB') as [unallocated space]
-- from second result set of 'exec sp_spacedused'
,ltrim(str(pages.reservedpages * 8192 / 1024.,15,0) + ' KB') as [reserved]
,ltrim(str(pages.pages * 8192 / 1024.,15,0) + ' KB') as data
,ltrim(str((pages.usedpages - pages.pages) * 8192 / 1024.,15,0) + ' KB') as index_size
,ltrim(str((pages.reservedpages - pages.usedpages) * 8192 / 1024.,15,0) + ' KB') as unused
-- additional columns data and Log Size
,ltrim(str((convert (dec (15,2),sf.dbsize)) * 8192 / 1048576,15,2) + ' MB') as dbsize
,ltrim(str((convert (dec (15,2),sf.logsize)) * 8192 / 1048576,15,2) + ' MB') as logsize
--INTO dbo.testing9999
FROM (
select
database_id,
sum(convert(bigint,case when type = 0 then size else 0 end)) as dbsize,
sum(convert(bigint,case when type <> 0 then size else 0 end)) as logsize,@@ServerName as ServerName
from sys.master_files
group by database_id
) sf,
(
SELECT
dbname,
reservedpages,
usedpages,
pages
FROM @ALLOCATION_TABLE
) pages
WHERE DB_NAME(sf.database_id)=pages.dbname
October 8, 2015 at 8:50 am
will take a stab.
You might need to have a limited user,X, created and then give access to each system table needed to each user X for each DB
So this is probably the least you need to do,
have a login say, test created Then
use master
GRANT SELECT ON sys.DATABASES TO test ;
EXEC sp_MSforeachdb N' use ? create user test for login test'
EXEC sp_MSforeachdb N'GRANT SELECT ON ?.sys.internal_tables to test'
EXEC sp_MSforeachdb N'GRANT SELECT ON ?.sys.partitions to test'
and so on for the specific tables you need.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply