Get Data and Log File Sizes

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Author: Unknown

Date: 23 Jun 2009

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.

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