This entry is part 1 of a 7 part series. To jump to a particular entry, you can click any of the following links:

• Log Shipping – What it is
• Planning – Laying it all out before setting it up
• Preparing your Servers
• Creating a maintenance plan
• When bad things happen to good servers – issues I’ve run into and how I fixed them
• Hands free monitoring
• Flipping the switch – How to do your manual failover

Once our logshipping was set up at Genesis Systems, we wanted an easier way to watch for errors.  Our process can fail in a number of places such as creating the trn file, copying the trn file to the destination server, or applying the trn file to the destination database.  Each of these 3 steps is a job inside of SQL Server.

We found that sometimes one or more of our jobs would step outside the threshold and then the logshipping monitor was pumping out error messages once per minute per logshipping pair until the next cycle and then the process would 'catch itself back up'.

Our solution was to write a report that could be run at a specified interval and let us know how our jobs were looking.  Basically, this is a stored procedure that checks the logshipping system tables, compares the last backup/copy times against the current time and the threshold. If outside, send an email notification.  This was created as an HTML email just so it looked better.

I was going to put the stored procedure in this article, but it's too big.  Click HERE to get the code.  In our office it runs every morning and sends out a report of databases that are suspect (where the last update is more than the backup frequency, but still within the out-of-sync threshold) or in error (where the last update is more than the out-of-sync threshold).  We also check to make sure the jobs are enabled (just in case somehow one of our copy/load jobs get disabled).  We send out our email notifications using CDOSysMail.  If you need the mail code, you can check it out HERE.

 Here's a sample of the output:

Subject: Logshipping Report - 0 Error(s), 3 Suspect(s)

 ERRORS (0)
No Errors to report


Suspect (3)