Analysis - Data file used space

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Analysis - Data file used space

Postby Andrew Hodge » Tue Apr 10, 2012 10:32 am

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
Andrew Hodge
 
Posts: 90
Joined: Thu Jul 19, 2007 8:34 am
Location: Kent, UK

Postby Chris Spencer » Tue Apr 10, 2012 1:57 pm

Hi Andrew

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

Thank you for the feedback.

Regards
Chris
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Re: Analysis - Data file used space

Postby EdCarden » Fri Apr 13, 2012 5:10 pm

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.

Code: Select all
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
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests