Find the database size (log/row/total) in SQL Server using T-SQL

Today, one of my colleague asked me how to find the database size in SQL Server using T-SQL.

The below script would help you to get the information:


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

EDITed on 22nd Aug 2016
The below script can be used to identify the same for all databases:


CREATE TABLE TEMP_FILESIZE(DBNAME sysname, log_size varchar(100),row_size varchar(100), total_size varchar(100))
Insert into TEMP_FILESIZE
EXEC sp_msforeachdb '
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 

		SELECT 
			  database_name = ''?''
			, 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
end 
' 
Select * From TEMP_FILESIZE Order by log_size desc
DROP Table TEMP_FILESIZE
Advertisements

One thought on “Find the database size (log/row/total) in SQL Server using T-SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s