Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



T-SQL Get Total Database Size
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-10-13








sys.databases
- Contains one row per database in the instance of SQL Server.


sys.master_files
- Contains a row per file of a database as stored in the master database. This is a single, system-wide view.
- size column = Current file size, in 8-KB pages.



Total Size of all Databases:

SELECT ROUND(SUM(size) * 8 / 1024, 0) as 'All Databases Total Size (MB)' from sys.master_files


Total Size of Each Database:

SELECT upper(d.name) as 'Database', ROUND(SUM(mf.size) * 8 / 1024, 0) as 'Size (MB)'
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
GROUP BY d.name
ORDER BY d.name


Total Size of Each Database by File Type:

SELECT upper(d.name) as 'Database', mf.type_desc as 'File Type', ROUND(SUM(mf.size) * 8 / 1024, 0) as 'Size (MB)'
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
GROUP BY d.name, mf.type_desc
ORDER BY d.name