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 ; 

SELECT  db.dbid AS 'DB ID', AS 'Database 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);


Post a Comment

By all means leave a comment. I may not be able to get back to you as quickly as I'd like but I'll do my very best.