Analysis - Data file used space

Andrew HodgeAndrew Hodge Posts: 132
edited April 13, 2012 12:10PM in SQL Monitor Previous Versions
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

Comments

  • 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
  • EdCardenEdCarden Posts: 134 Silver 1
    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
    
Sign In or Register to comment.