barnesco
asked on
How to aggregate database sizes into one total
How would you aggregate (total) all returned rows into one number. I have so far the embedded code. Thanks
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + CHAR(13) + 'UNION ALL
' ,'') + 'SELECT ' +
'sum(size * 8 /1024.0)/1000 AS GB from ' + QUOTENAME(name) + '.dbo.sysfiles'
FROM sys.databases
ORDER BY name
EXECUTE (@SQL)ASKER
I'm trying to find the sum of all database sizes distilled into one number. I did accidentally include some extra code, so this will clean it up:
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + CHAR(13) + 'UNION ALL
' ,'') + 'SELECT ' + 'sum(size * 8 /1024.0)/1000 AS GB from .dbo.sysfiles'
FROM sys.databases
EXECUTE (@SQL)
Here's some points to consider along with the code below:
1) I've separated data size and log size, as log size can vary considerably over time (esp. if you shrink log files a lot: definitely not recommended, but many people still do it).
2) dbo.sysfiles is such an ancient view that I switched to using sys.database_files instead
3) You might want to consider tempdb separately, although for now I left it in the totals.
SELECT data_size_gb, log_size_gb, data_size_gb + log_size_gb AS total_gb
FROM (
SELECT
CAST(SUM(CASE WHEN df.type_desc = 'LOG' THEN 0 ELSE df.size END) / 128.0 / 1000.0 AS decimal(9, 3)) AS data_size_gb,
CAST(SUM(CASE WHEN df.type_desc = 'LOG' THEN df.size ELSE 0 END) / 128.0 / 1000.0 AS decimal(9, 3)) AS log_size_gb
FROM sys.database_files df
) AS derived
1) I've separated data size and log size, as log size can vary considerably over time (esp. if you shrink log files a lot: definitely not recommended, but many people still do it).
2) dbo.sysfiles is such an ancient view that I switched to using sys.database_files instead
3) You might want to consider tempdb separately, although for now I left it in the totals.
SELECT data_size_gb, log_size_gb, data_size_gb + log_size_gb AS total_gb
FROM (
SELECT
CAST(SUM(CASE WHEN df.type_desc = 'LOG' THEN 0 ELSE df.size END) / 128.0 / 1000.0 AS decimal(9, 3)) AS data_size_gb,
CAST(SUM(CASE WHEN df.type_desc = 'LOG' THEN df.size ELSE 0 END) / 128.0 / 1000.0 AS decimal(9, 3)) AS log_size_gb
FROM sys.database_files df
) AS derived
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CORRECTION:
I needed to use "sys.master_files" rather than "sys.database_files".
There is sometimes a slight lag before the sys.master_files view reflects db size changes, but it shouldn't really be significant, and a query against master_files will run much faster.
SELECT data_size_gb, log_size_gb, data_size_gb + log_size_gb AS total_gb
FROM (
SELECT
CAST(SUM(CASE WHEN mf.type_desc = 'LOG' THEN 0 ELSE mf.size END) / 128.0 / 1000.0 AS decimal(9, 3)) AS data_size_gb,
CAST(SUM(CASE WHEN mf.type_desc = 'LOG' THEN mf.size ELSE 0 END) / 128.0 / 1000.0 AS decimal(9, 3)) AS log_size_gb
FROM sys.master_files mf
) AS derived
I needed to use "sys.master_files" rather than "sys.database_files".
There is sometimes a slight lag before the sys.master_files view reflects db size changes, but it shouldn't really be significant, and a query against master_files will run much faster.
SELECT data_size_gb, log_size_gb, data_size_gb + log_size_gb AS total_gb
FROM (
SELECT
CAST(SUM(CASE WHEN mf.type_desc = 'LOG' THEN 0 ELSE mf.size END) / 128.0 / 1000.0 AS decimal(9, 3)) AS data_size_gb,
CAST(SUM(CASE WHEN mf.type_desc = 'LOG' THEN mf.size ELSE 0 END) / 128.0 / 1000.0 AS decimal(9, 3)) AS log_size_gb
FROM sys.master_files mf
) AS derived
ASKER
Thanks, that worked
Open in new window