In a previous post I demonstrated some SQL Server 2005 queries that would identify when a log shipping target was out of date, this post includes those queries in a stored procedure that will alert you when the last applied log is older than the timeframe you specify.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[spCheckLog]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spCheckLog]
GO
create procedure spCheckLog
@database sysname,
@duration_in_mins int,
@debug bit = 0
AS
/*
Author: Alex Wilson
Date: 20080923
Version: 1.0
Purpose: Check the metadata for the last applied log, if its considered too
old against the input parameters it will throw an alert
Usage: EXEC spCheckLog @database, @duration
EXEC spCheckLog 'poker', 20
*/
declare @Last datetime
select top 1
@Last = restore_date
from msdb..restorehistory rh with(nolock)
join msdb..backupset bs with(nolock)
on bs.backup_set_id = rh.backup_set_id
where rh.destination_database_name = @database
order by restore_date desc
if @debug = 1
begin
print 'Database Parameter (@database) : ' + cast(@database as varchar)
print 'Too Old Parameter (@duration_in_mins) : ' + cast(@duration_in_mins as varchar)
print 'Current Server Time : ' + cast(getdate() as varchar)
print 'Last Log Applied : ' + cast(@last as varchar)
end
if datediff(mi, @last, getdate()) >= @duration_in_mins
begin
print 'Sending Alert Email...'
/*send alert email here */
Print '...DONE'
end