Analysis - Data file used space

Andrew Hodge

Just looking through the analysis section on monitor 3 and have noticed that there isnt a data file space used (equivelent to the log space used).

The data size just returns the size of the mdf files which isnt very useful.

I take it we could develop this within the custom reports but think this should probably be included in the base reports

Chris Spencer

Hi Andrew

I've raised this as an enhancement request (ref: SRP-6485).

Thank you for the feedback.

Regards
Chris

EdCarden

Andrew Hodge wrote:

Just looking through the analysis section on monitor 3 and have noticed that there isnt a data file space used (equivelent to the log space used).

The data size just returns the size of the mdf files which isnt very useful.

I take it we could develop this within the custom reports but think this should probably be included in the base reports

Out of curiosity why are you wanting an analysis of %File Used on the Data file?

BTW - You can write a custom alert/mertic in version 3.x to catch a change in the percent used value of your database file. The below T-SQL will give you the values for the data and log files which you can then assign to an Alert and then monitor it for when the Percent Used value exceeds some designated value.

create table #data(Fileid       int NOT NULL,
                   [FileGroup]  int NOT NULL,
                   TotalExtents int NOT NULL,
                   UsedExtents  int NOT NULL,
                   [Name]       sysname NOT NULL,
                   [FileName]   varchar(300) NOT NULL)


create table #log(dbname       sysname NOT NULL,
                  LogSize      numeric(15,7) NOT NULL,
                  LogUsed      numeric(9,5) NOT NULL,
                  Status       int NOT NULL)


insert #data exec('DBCC showfilestats with no_infomsgs')
insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

WITH CTE_X AS
(
select 'DATA' as [Type],
       [Name], 
       (TotalExtents*64)/1024.0 as [TotalMB],
        (UsedExtents*64)/1024.0 as [UsedMB]
        
from #data 
union all
select 'LOG', db_name() + ' LOG', LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()
)


select t.type, t.name, t.totalMB, t.UsedMB, convert(numeric(5,2),(t.usedMB/t.totalMB)*100) AS [PercentUsed]
from CTE_X t

drop table #data
drop table #log