Cool Tips‎ > ‎Data/Database Size‎ > ‎

Data/Database Size - Get Data and Log File Sizes

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        23 Jun 2009

Description

Retrieves a list of database and log file sizes by querying the master..sysperfinfo view (interface to selected Windows perfmon counters).
This information is augmented with log file space used and percent used figures.
 

Code

SELECT instance_name AS 'Database Name',
  
MAX(CASE
          
WHEN counter_name = 'Data File(s) Size (KB)'
              
THEN cntr_value
          
ELSE 0
      
END) AS 'Data File(s) Size (KB)',
  
MAX(CASE
          
WHEN counter_name = 'Log File(s) Size (KB)'
              
THEN cntr_value
          
ELSE 0
      
END) AS 'Log File(s) Size (KB)',
  
MAX(CASE
          
WHEN counter_name = 'Log File(s) Used Size (KB)'
              
THEN cntr_value
          
ELSE 0
      
END) AS 'Log File(s) Used Size (KB)',
  
MAX(CASE
          
WHEN counter_name = 'Percent Log Used'
              
THEN cntr_value
          
ELSE 0
      
END) AS 'Percent Log Used'
FROM master..sysperfinfo
WHERE counter_name IN
  
(
      
'Data File(s) Size (KB)',
      
'Log File(s) Size (KB)',
      
'Log File(s) Used Size (KB)',
      
'Percent Log Used'
  
)
  AND
instance_name != '_total'
GROUP BY instance_name


ċ
Get data and log file sizes.sql
(1k)
Andy Hughes,
Jun 18, 2012, 10:59 AM
Comments