Time since last restore

July 2, 2012 by SQL Monitor Team | 0 comments | 552 views
Categories: Auditing

Rating: 0.0/5 (0 votes cast)

This metric tracks the number of minutes since the most recent restore of the database.

If your log restores aren’t happening when they’re meant to, you want to know about it. You’ll be relying on restoring from logs should anything happen to your databases, and if you can’t restore to a certain point in time, you risk losing valuable data.

For more information about restoring data, see: http://msdn.microsoft.com/en-us/library/ms177446.aspx

Note: This metric works on SQL Server 2000 SP4 and later versions.

Note: An alert can only be raised after at least once restore has occurred.

Metric definition

Metric Name:

Last restore time

Description:

Measure the time since the last restore, in minutes.

Enter the T-SQL query that will collect data:

SELECT DATEDIFF(MINUTE, restore_date, GETDATE())
  FROM (SELECT TOP 1 restore_date
          FROM msdb.dbo.restorehistory
          WHERE destination_database_name = DB_NAME()
          ORDER BY restore_date DESC
       ) rd;

Select instances to collect from:

Select the instance that contains a database that is regularly restored via log shipping.

Choose databases to collect from:

Specify databases and type

The database you're interested in

Set collection frequency:

Collect data every minute

Use collected or calculated values:

Leave the Use a calculated rate of change between collections check box turned off

Metric collection is:

Enabled

Alert definition

Alert name:

Log restoration overdue

Description:

The time since the last log restoration is too high - it should have happened already.

Raise an alert when the metric value goes:

above the defined thresholds

Default threshold values:

Set the thresholds as follows:

High:	122
Medium:	65
Low:	32

Note: These thresholds are intended as guideline values and are based on a log shipping frequency of 30 minutes. If they seem too high or too low in practise, replace them with values that match your log shipping frequency.

Raise an alert when the threshold is passed for:

Enter 1 collection

This alert is:

Enabled