Last backup size

| 2 Comments | 9,680 views

VN:F [1.9.17_1161]
Rating: 3.7/5 (3 votes cast)

Backups are at the heart of the activities of any DBA. You have to restore the backup to know it is good, but you can get an early alert that something is wrong if your backup size changes rapidly or unexpectedly in a short period of time. It may be because a large amount of data has been inserted, or deleted if the metrics goes down, and this could be a concern if you were not expecting it. It could also be because the backup process did not complete correctly and you have a different problem that needs your attention.

Last backup size, 3.7 out of 5 based on 3 ratings

Metric definition

Metric Name:
Last full backup size (MB)
The size of the last Full Database backup for each database, in MB. When reviewing the analysis graph, look for large, steep rises or falls that indicate rapid changes in size. For example, you might expect backup size to track data size so potentially on a slow upward trend over a long time with seasonal drops relating to when data is archived. Monitoring system databases means that you can check to see if anyone accidentally creates a table in Master and fills it with data because they didn’t change their default database setting.
Enter the T-SQL query that will collect data:
SELECT TOP 1 [b].[backup_size] / (1024 * 1024.0) AS [LastBackUp_MB]
  FROM [msdb].[dbo].[backupfile] AS b
  INNER JOIN [msdb].[dbo].[backupset] AS b2
    [b].[backup_set_id] = [b2].[backup_set_id] 
  INNER JOIN [sys].[sysfiles] AS s
    [b].[logical_name] COLLATE DATABASE_DEFAULT = [s].[name]
  WHERE [b].[file_type] = 'D'	-- Log backups have a file-type of 'L' but will have a range of sizes.
  ORDER BY [b2].[backup_start_date] DESC;
-- If you have a mixture of collations on your server you will need to build this join using a COLLATE value to JOIN to MSDB e.g.;
-- if MSDB has a collation of Latin1_General_CI_AS you will need to write the join as "ON [b].[logical_name] = [s].[name] COLLATE Latin1_General_CI_AS"
Select instances to collect from:
Select all
Choose databases to collect from:
All databases
Set collection frequency:
Collect data every hour
Note: This needs to follow your backup schedule. Probably no more frequent than every 30 minutes but potentially daily would be acceptable.
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:

Alert definition

Setting an alert on this is difficult as the starting size of the database has to be known and as such the alert will then need to be specified for every database.

VN:F [1.9.17_1161]
Rating: 3.7/5 (3 votes cast)
  • rob

    I would like to see a similar thing, but for restore duration. We refresh enviornments every day so it would be nice to track as the database grows how long are servers taking to restore. Restore speed compared to Database Size used.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • Pingback: Avoiding a DBA’s Worst Days with Monitoring | Voice of the DBA()