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
|
|
|
|
|
|