Users: 30k; Pages Per Session: 2.5p; Avg. Session Duration: 1:35mi
TechDevOps.com
Explore Tools
{Featuring Microsoft Technologies + Enterprise Data + Cloud Computing}



T-SQL Get SQL Server Transaction Log Backup Size (or Database Backup Size)
by BFarrell (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2017-08-25









T-SQL:


Declare @db sysname = 'xyz';
Declare @type varchar(10) = 'L';
Declare @durationindays int = 7

Select
bs.Server_Name,
bs.Database_Name,
Case bs.Type When 'L' Then 'Log' When 'D' Then 'Database' End as Type,
bs.backup_size 'BackupSize(B)',
(bs.backup_size/1024) 'BackupSize(KB)',
(bs.backup_size/1024)/1024 'BackupSize(MB)',
(bs.backup_size/1024)/1024/1024 'BackupSize(GB)',
bs.Backup_Start_Date,
bs.Backup_Finish_Date ,
bf.Physical_Device_Name,
bs.Recovery_Model,
bs.User_Name
FROM
MSDB..backupset bs
INNER JOIN msdb..backupmediafamily bf ON bf.media_set_id=bs.media_set_id
where
bs.type = @type and
bs.database_name = @db and
bs.backup_finish_date > GetDate() - @durationindays
order by backup_finish_date desc