Thursday, 8 October 2009

Data file sizes in SQL Server 2005

If you want to list all the data and log files plus their sizes try the following SQL:

USE master ; 
GO 

SELECT  db.dbid AS 'DB ID', 
        db.name AS 'Database Name', 
        af.name AS 'Logical Name', 
        af.[size] as 'File Size (in 8-kilobyte (KB) pages)', 
        (((CAST(af.[size] AS DECIMAL(18, 4)) * 8192) / 1024) / 1024) AS 'File Size (MB)', 
        ((((CAST(af.[size] AS DECIMAL(18, 4)) * 8192) / 1024) / 1024) 
          / 1024 ) AS 'File Size (GB)', 
        af.filename AS 'Physical Name' 
FROM    sys.sysdatabases db 
        INNER JOIN sys.sysaltfiles af ON db.dbid = af.dbid 
WHERE   [fileid] IN (1, 2);