02 July 2012

Last backup size

by Jonathan Allen

16,244 0 Auditing

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate 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.

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


		
			
			
Transact-SQL
			
			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
  ON
    [b].[backup_set_id] = [b2].[backup_set_id] 
  INNER JOIN [sys].[sysfiles] AS s
  ON
    [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"
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
				
						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
  ON
    [b].[backup_set_id] = [b2].[backup_set_id] 
  INNER JOIN [sys].[sysfiles] AS s
  ON
    [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"

Instances to collect from

Select all

Databases to collect from

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.

16,244 Rate

Click to rate this post!

[Total: 4 Average: 4]

Last backup size

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics