SQL server probe alloc_space Query

Document ID : KB000121940
Last Modified Date : 29/11/2018
Show Technical Document Details
Introduction:

 
Question:
I need the exact query used to find allocated space from the sqlserver probe.
Environment:
UIM 9.x and earlier
sqlserver probe 5.40 and earlier
Answer:
QOS_SQLSERVER_alloc_space    Percent    Monitors allocated space.

this is part of the Press free space query:

free_space,        
    
sp_MSforeachdb 'select ''?'' db_name, (select convert(bigint,sum(size))* 8192 from [?].sys.database_files where type <> 1) allocated,
ISNULL((select convert(bigint,sum(max_size))*8192 from [?].sys.database_files f where ((f.type <> 1) and f.growth > 0 and size > 0 ) having ((select count(*) from [?].sys.database_files where max_size = -1 and type <> 1 and growth > 0) = 0)),-1) max,
ISNULL((select convert(bigint,sum(size))*8192 from [?].sys.database_files where type <> 1 and growth = 0 and size > 0),-1) max2,
ISNULL((select convert(bigint,sum(a.used_pages))*8192 from [?].sys.allocation_units a),-1) used,
(select count(*) from [?].sys.database_files cg where max_size = -1 and type <> 1 and growth > 0) unlimited

The part of the query for this is:
sp_MSforeachdb 'select ''?'' db_name, (select convert(bigint,sum(size))* 8192 from [?].sys.database_files where type <> 1) allocated