May 04, 2016

SQL Tranlog shipping age check

I have a database setup with frequent tranlog shipping.  I then have a query running in some monitoring software (currently utilizing PRTG... it's cheap!).  There's probably a way to query the actual restore table, however we're more interested in the timestamp of the last meaningful transaction in the database so we're monitoring an internal audit table.  This is the basic query:
SELECT datediff(s, max([timestamp_column]), getdate()) /3600 from [audit_table];
Note: As you can see the query returns hours in decimal form.  Adjust as needed.

Anyway... sometimes the query tries to run when a restore is happening which causes errors.  A fix for this is to connect to master instead of directly to the database then run the query wrapped with an error and timer.  This query will attempt to run 3 times with a 10 second pause between.  The monitoring software is setup with a 120 timeout, so this query will error first.
DECLARE @Tries tinyint
SET @Tries = 1
WHILE @Tries <=3
BEGIN TRY
    SELECT datediff(s, max([timestamp_column]), getdate()) /3600 from [database].[dbo].[audit_table];
    BREAK
END TRY
BEGIN CATCH
    WAITFOR DELAY '00:00:10';
    SET @Tries = @Tries + 1
    CONTINUE
END CATCH