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(SELECTDB_ID(name) AS DBID,name AS DBNameFROM master..sysdatabases),cte_DataSize AS(SELECT DB_NAME(mf.database_id) AS DBName, SUM(CAST(mf.Size AS BIGINT)) * 8 * 1024 AS FileSizeFROM master.sys.master_files mfINNER JOIN cte_Databases dON mf.Database_ID = d.DBIDWHERE 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 FileSizeFROM master.sys.master_files mfINNER JOIN cte_Databases dON mf.Database_ID = d.DBIDWHERE mf.type_desc = 'LOG'GROUP BY DB_NAME(mf.database_id)),cte_BackupSizeFull AS(SELECTbs.Database_Name AS DBName,SUM(CAST(bs.backup_size AS BIGINT)) AS FullBackupSizeFROM msdb.dbo.backupset bsINNER JOIN msdb.dbo.backupmediafamily bmfON bmf.media_set_id = bs.media_set_idINNER JOIN msdb.dbo.backupfile bfON bf.backup_set_id = bs.backup_set_idINNER JOIN cte_Databases dON bs.Database_Name = d.DBNameWHERE 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(SELECTbs.Database_Name AS DBName,SUM(CAST(bs.backup_size AS BIGINT)) AS LogBackupSizeFROM msdb.dbo.backupset bsINNER JOIN msdb.dbo.backupmediafamily bmfON bmf.media_set_id = bs.media_set_idINNER JOIN msdb.dbo.backupfile bfON bf.backup_set_id = bs.backup_set_idINNER JOIN cte_Databases dON bs.Database_Name = d.DBNameWHERE bs.Backup_Start_Date >= DATEADD(hh, -24, GETDATE())AND bs.Type = 'l'AND bf.File_Type = 'l'GROUP BY bs.Database_Name)SELECTUPPER(ds.DBName) AS DBName,ds.FileSize AS DataSize,ls.FileSize AS LogSize,bsf.FullBackupSize AS FullBackupSize,bsl.LogBackupSize AS LogBackupSizeFROM cte_DataSize dsINNER JOIN cte_LogSize lsON ds.DBName = ls.DBNameLEFT OUTER JOIN cte_BackupSizeFull bsfON ds.DBName = bsf.DBNameLEFT OUTER JOIN cte_BackupSizeLog bslON ds.DBName = bsl.DBNameORDER 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 */