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