MS SQL

Display data and log space information for each database – what’s eating up server space?

This thread outlines how to display data and log space information for all the databases in SQL Server. This will give you an insight into which database needs to be maintained or dropped to save space on your server.

  1. Connect to the Database Engine.
  2. From the Standard bar, click New Query.
  3. Copy and paste the following example into the query window and click Execute.
SELECT sd.NAME as PhysicalName,smf.physical_name FileLocation
,smf.NAME as LogicalName
,smf.type_desc
,(CAST(smf.size AS FLOAT) * 8096) / (1024 * 1024 * 1024) AS SizeGB
,(CAST(smf.size AS FLOAT) * 8096) AS SizeBytes
,(CAST(smf.size AS FLOAT) * 8096) / (1024) AS SizeKB
,(CAST(smf.size AS FLOAT) * 8096) / (1024 * 1024) AS SizeMB
--,sd.log_reuse_wait_desc
--,sd.recovery_model_desc
--,*
FROM sys.databases sd
INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id
WHERE
smf.type_desc IN (
'LOG','ROWS'
    ) 
 ORDER BY SizeGB desc , sd.NAME

one may uncomment a few lines to get more fields.

Log types are for MDF files and Rows types are for LDF files

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.