Last backup size

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.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

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

Name

Last full backup size (MB)

Description

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.

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

0

Collection frequency

3600

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 unchecked

Metric collection

Enabled

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.