SQL Server - Size of each database

Compatible from SQL Server 2008

Returns size of all databases all database files with its location and state.

Script source:

SELECT
	@@SERVERNAME AS InstanceName,
    db.name AS DatabaseName,
    mf.Name AS FileType,
    mf.physical_name AS PhysicalFile,
    mf.state_desc AS FileStatus,
    (mf.size*8)/1024 AS FileSizeMB,
    mf.size*8 AS SizeInBytes
FROM 
    sys.master_files mf
    INNER JOIN sys.databases db ON db.database_id = mf.database_id
ORDER BY db.name

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
InstanceNamesysnameName of SQL Server instance
DatabaseNamesysnameName of database.
FileNamesysnameLogical name of the file in the database.
PhysicalFilenvarchar(260)Operating-system file name.
FileStatusnvarchar(60)Description of the file state: ONLINE RESTORING RECOVERING RECOVERY_PENDING SUSPECT OFFLINE DEFUNCT
FileSizeMBintSize of given database file in MB.

Leave a Reply

Your email address will not be published. Required fields are marked *