Get Database and Backup File Sizes
Applicability:
SQL Server 2000: Not Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Tested
SQL Server 2014: Tested
SQL Server 2016: Tested
SQL Server 2017: Not Tested
Credits:
Author: ChillyDBA
Date: 6 Jun 2020
Description
List the database and log file sizes plus the latest database and log backup files sizes for all databases on a server.
Code
;WITH cte_Databases AS
(
SELECT
DB_ID(name) AS DBID
,name AS DBName
FROM master..sysdatabases
)
,cte_DataSize AS
(
SELECT DB_NAME(mf.database_id) AS DBName, SUM(CAST(mf.Size AS BIGINT)) * 8 * 1024 AS FileSize
FROM master.sys.master_files mf
INNER JOIN cte_Databases d
ON mf.Database_ID = d.DBID
WHERE mf.type_desc = 'ROWS'
GROUP BY DB_NAME(mf.database_id)
)
,cte_LogSize AS
(
SELECT DB_NAME(mf.database_id) AS DBName, SUM(CAST(mf.Size AS BIGINT)) * 8 * 1024 AS FileSize
FROM master.sys.master_files mf
INNER JOIN cte_Databases d
ON mf.Database_ID = d.DBID
WHERE mf.type_desc = 'LOG'
GROUP BY DB_NAME(mf.database_id)
)
,cte_BackupSizeFull AS
(
SELECT
bs.Database_Name AS DBName
,SUM(CAST(bs.backup_size AS BIGINT)) AS FullBackupSize
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bmf.media_set_id = bs.media_set_id
INNER JOIN msdb.dbo.backupfile bf
ON bf.backup_set_id = bs.backup_set_id
INNER JOIN cte_Databases d
ON bs.Database_Name = d.DBName
WHERE bs.Backup_Start_Date >= DATEADD(hh, -24, GETDATE())
AND bs.Type = 'd'
AND bf.File_Type = 'd'
GROUP BY bs.Database_Name
)
,cte_BackupSizeLog AS
(
SELECT
bs.Database_Name AS DBName
,SUM(CAST(bs.backup_size AS BIGINT)) AS LogBackupSize
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bmf.media_set_id = bs.media_set_id
INNER JOIN msdb.dbo.backupfile bf
ON bf.backup_set_id = bs.backup_set_id
INNER JOIN cte_Databases d
ON bs.Database_Name = d.DBName
WHERE bs.Backup_Start_Date >= DATEADD(hh, -24, GETDATE())
AND bs.Type = 'l'
AND bf.File_Type = 'l'
GROUP BY bs.Database_Name
)
SELECT
UPPER(ds.DBName) AS DBName
,ds.FileSize AS DataSize
,ls.FileSize AS LogSize
,bsf.FullBackupSize AS FullBackupSize
,bsl.LogBackupSize AS LogBackupSize
FROM cte_DataSize ds
INNER JOIN cte_LogSize ls
ON ds.DBName = ls.DBName
LEFT OUTER JOIN cte_BackupSizeFull bsf
ON ds.DBName = bsf.DBName
LEFT OUTER JOIN cte_BackupSizeLog bsl
ON ds.DBName = bsl.DBName
ORDER BY ds.DBName
/*
SELECT *
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bmf.media_set_id = bs.media_set_id
INNER JOIN msdb.dbo.backupfile bf
ON bf.backup_set_id = bs.backup_set_id
WHERE bs.Backup_Start_Date >= DATEADD(hh, -24, GETDATE())
AND bs.Type = 'l'
AND bf.File_Type = 'l'
and bs.database_name = 'XXXXXXXX'
select * FROM msdb.dbo.backupset
*/