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 Size of Each Database:
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
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
|
|
|
|
|
|