SQL diagnostic commands

List database and respective space usage

SELECT database_name = DB_NAME(database_id)
 , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
 , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
 , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
 FROM sys.master_files WITH(NOWAIT)
 WHERE database_id = DB_ID() -- for current db
 GROUP BY database_id;

File Space used by database

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

State of database log files

SELECT [name],[log_reuse_wait],log_reuse_wait_desc] FROM [sys].[databases];

Leave a Reply

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