Home » How to get the sizes of all databases on a SQL Server

How to get the sizes of all databases on a SQL Server

How to get the sizes of all databases on a SQL Server ?





To get database size information of all databases on a SQL Server, we use sys.master_files .It contains a row per file of a database for all databases.

Here is the T-SQL Query for this –

SELECT DB_NAME(database_id) AS DataBaseName,
CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE Type_Desc END AS FileType,
CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )AS TotalSizeInMB
FROM sys.master_files
GROUP BY GROUPING SETS
(
(DB_NAME(database_id), Type_Desc),
(DB_NAME(database_id))
)
ORDER BY DB_NAME(database_id), Type_Desc DESC
GO

In case, If we want to see size of any particular database then we can modify above query as

SELECT DB_NAME(database_id) AS DataBaseName,
CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN Type_Desc = 'LOG' THEN 'Log File(s)' 
ELSE Type_Desc END AS FileType, 
CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )AS TotalSizeInMB 
FROM sys.master_files 
WHERE database_id = DB_ID('SqlTutorials')
GROUP BY GROUPING SETS 
(
 (DB_NAME(database_id), Type_Desc), 
 (DB_NAME(database_id)) 
) 
ORDER BY DB_NAME(database_id), Type_Desc DESC
GO




Leave a Reply

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