Analysis - Data file used space

• 
  Hi Andrew
  
  I've raised this as an enhancement request (ref: SRP-6485).
  
  Thank you for the feedback.
  
  Regards
  Chris

  Chris Spencer April 10, 2012 08:57

  - 0 +
• 

  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
  

  EdCarden April 13, 2012 12:10

  - 0 +

Add comment

Please sign in to leave a comment.