Cool Tips‎ > ‎Backup and Restore‎ > ‎

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



ċ
Andy Hughes,
Jun 6, 2020, 7:44 AM
Comments