note to self: SQL log shipping stats/graphs

Results of query coupled with google spreadsheets w/ timeline graph applied, equate to pretty data worth sharing.

--// View last log backups per half hour, total size(MB), with number of logs
SELECT TOP 500
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0) DT30
  , SUM(backup_size / 1024 / 1024) SizeMB
  , COUNT(DISTINCT media_set_id) TrnCount
FROM
    msdb..backupset (NOLOCK)
WHERE
    type = 'L'
GROUP BY
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0)
ORDER BY
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0) DESC



Note: can easily work for Data backups too, just change "type = 'L'" to "type = 'D'"

~Enjoy

Comments :

0 comments to “note to self: SQL log shipping stats/graphs”