Blogs
 
Tuesday, December 20, 2005

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

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)

Primary: SourceServer.Database1
Secondary: DestServer.Database1
Backup Frequency: 5 minutes
Backup Threshold: 15 minutes
Out Of Sync Threshold: 15 minutes
Primary Threshold Alert Enabled: Yes
Secondary Threshold Alert Enabled: Yes
Copy Enabled: Yes
Load Enabled: Yes
Last Updated: Dec 10 2005 7:54AM
Last Copy: Dec 10 2005 7:54AM
Last Upload: Dec 10 2005 7:54AM
Last Backup File: Database1_tlog_200512100755.TRN
Last Copied File: Database1_tlog_200512100750.TRN
Last Uploaded File: Database1_tlog_200512100750.TRN
This Logshipping pair is showing the following alert(s):
  • The database uploads have exceeded the upload frequency.


Primary: SourceServer.Database2
Secondary: DestServer.Database2
Backup Frequency: 720 minutes
Backup Threshold: 2160 minutes
Out Of Sync Threshold: 2160 minutes
Primary Threshold Alert Enabled: Yes
Secondary Threshold Alert Enabled: Yes
Copy Enabled: Yes
Load Enabled: Yes
Last Updated: Dec 10 2005 12:00AM
Last Copy: Dec 9 2005 11:59AM
Last Upload: Dec 9 2005 12:00PM
Last Backup File: Database2_tlog_200512100000.TRN
Last Copied File: Database2_tlog_200512090000.TRN
Last Uploaded File: Database2_tlog_200512090000.TRN
This Logshipping pair is showing the following alert(s):
  • The database uploads have exceeded the upload frequency.


Primary: SourceServer.Database3
Secondary: DestServer.Database3
Backup Frequency: 15 minutes
Backup Threshold: 45 minutes
Out Of Sync Threshold: 45 minutes
Primary Threshold Alert Enabled: Yes
Secondary Threshold Alert Enabled: Yes
Copy Enabled: Yes
Load Enabled: Yes
Last Updated: Dec 10 2005 7:59AM
Last Copy: Dec 10 2005 7:44AM
Last Upload: Dec 10 2005 7:44AM
Last Backup File: Database3_tlog_200512100800.TRN
Last Copied File: Database3_tlog_200512100745.TRN
Last Uploaded File: Database3_tlog_200512100730.TRN
This Logshipping pair is showing the following alert(s):
  • The database uploads have exceeded the upload frequency.


Chris Antoniak  DBA/Developer



Archive

Recent Entries

Blogs we read


Page 1 Of 1 (1 items)