Calculate total size of transaction log backups in the last 24 hours

Question

I wanted to get the total backup sizes in KB, MB and GB for each database on an instance. I have pieced together the below but haven't been able to do the sum part.

SELECT  
   A.[Server],
    A.DATABASE_NAME,   
   A.last_db_backup_date,  
   B.backup_start_date,  
   B.expiration_date, 
   CAST(CAST(b.backup_size / 1000 AS INT) AS VARCHAR(14)) + ' ' + 'kB' AS bkSize,
   CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'mB' AS bkSize,
   CAST(CAST(b.backup_size / 1000000000 AS INT) AS VARCHAR(14)) + ' ' + 'gB' AS bkSize,    
   B.logical_device_name,  
   B.physical_device_name,   
   B.backupset_name, 
   B.description 
FROM 
   ( 
   SELECT   
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
       msdb.dbo.backupset.database_name,  
       msdb.dbo.backupset.backup_finish_date AS last_db_backup_date 
   FROM    msdb.dbo.backupmediafamily  
       INNER JOIN msdb.dbo.backupset 
       ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
   WHERE   msdb..backupset.type = 'l' 
   --GROUP BY 
   --    msdb.dbo.backupset.database_name  
   ) AS A 

   LEFT JOIN  

   ( 
   SELECT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'l' 
   ) AS B 
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] 
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()   
ORDER BY  
   A.database_name

Answer 1

You need to avoid casting your backup size to varchar or you will not be able to sum the values. If you need to then you can do that conversion afterwards.

Other than that it is just a matter of wrapping the whole lot up as a subquery, grouping the results by database name, and doing the sum (You may be able to do it other ways but that seemed the least amount of re-working for me).

    select subquery.DATABASE_NAME,
        SUM(bkSize_KB) as SumBkSize_KB,
        SUM(bkSize_MB) as SumBkSize_MB,
        SUM(bkSize_GB) as SumBkSize_GB
from
(
        SELECT  
           A.[Server],
            A.DATABASE_NAME,   
           A.last_db_backup_date,  
           B.backup_start_date,  
           B.expiration_date, 
           CAST(b.backup_size / 1000 AS INT) AS bkSize_KB,
           CAST(b.backup_size / 1000000 AS INT) AS bkSize_MB,
           CAST(b.backup_size / 1000000000 AS INT) AS bkSize_GB,
           B.logical_device_name,  
           B.physical_device_name,   
           B.backupset_name, 
           B.description 
        FROM 
           ( 
           SELECT   
               CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
               msdb.dbo.backupset.database_name,  
               msdb.dbo.backupset.backup_finish_date AS last_db_backup_date 
           FROM    msdb.dbo.backupmediafamily  
               INNER JOIN msdb.dbo.backupset 
               ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
           WHERE   msdb..backupset.type = 'l' 
           --GROUP BY 
           --    msdb.dbo.backupset.database_name  
           ) AS A 

           LEFT JOIN  

           ( 
           SELECT   
           CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
           msdb.dbo.backupset.database_name,  
           msdb.dbo.backupset.backup_start_date,  
           msdb.dbo.backupset.backup_finish_date, 
           msdb.dbo.backupset.expiration_date, 
           msdb.dbo.backupset.backup_size,  
           msdb.dbo.backupmediafamily.logical_device_name,  
           msdb.dbo.backupmediafamily.physical_device_name,   
           msdb.dbo.backupset.name AS backupset_name, 
           msdb.dbo.backupset.description 
        FROM   msdb.dbo.backupmediafamily  
           INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
        WHERE  msdb..backupset.type = 'l' 
           ) AS B 
           ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] 
        WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()   


) subquery

group by subquery.DATABASE_NAME