info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



T-SQL Get Total Database Size
by BF (Principal Consultant; Architecture; Engineering)
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
SELECT ROUND(SUM(convert(bigint,size)) * 8 / 1024, 0) as 'All Databases Total Size (MB)' from sys.master_files


--Total User Database Size by File Type:

SELECT mf.type_desc as 'File Type', ROUND(SUM(convert(bigint,mf.size)) * 8 / 1024 / 1024, 0) as 'Size (GB)'
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4
GROUP BY mf.type_desc


--Total User Database Size By Name:

SELECT upper(d.name) as 'Database', ROUND(SUM(convert(bigint,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
WHERE d.database_id > 4
GROUP BY d.name
ORDER BY d.name


--Total User Database Size By Name 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
--WHERE mf.type_desc = 'LOG'
--WHERE mf.type_desc = 'ROWS'
WHERE d.database_id > 4
GROUP BY d.name, mf.type_desc
ORDER BY d.name