Get Database and Backup File Sizes

;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

*/