Most of you who have Log Shipping in your environment has got the log shipping alert emails atleast once. Have you ever wondered how they are generated? Let us try to find more about the Log Shipping alert job.
Log Shipping is a way of shipping the transaction logs to another server so that we have a warm standby server in need of disaster recovery. I am not going to detail about Log Shipping and you can read it here. As you all know there are mainly 4 jobs which are created when we use Log Shipping . They are
1) Backup Job
2) Copy Job
3) Restore Job.
4) Alert Job
The backup Job backs up the transaction logs at specified intervals at the primary server. The copy jobs responsibility is to copy the transaction logs backed up by the backup job and place it in the backup folder we have specified in the secondary server. The restore job scans the backup folder in the secondary server and then restores the transaction logs to the secondary server db based on the log sequence number.
The alert job generates alerts, which are sent to Operators, when specific condition occur with respect to the backup or restore. These conditions relate to the other three jobs. If the backup job, copy job or the restore job fails or doesn’t run successfully in the defined interval then the alert is being fired. We can define these alerts when we set up log shipping. There is no specific alert we can create for the copy job but as the copy job directly effects the restore job , we can safely say that there is a problem with one of these jobs if we get an alert. The alert job itself is scheduled to run every 2 minutes.
What the alert job basically does is to run a stored procedure sys.sp_check_log_shipping_monitor_alert in the msdb database. The stored procedure basically checks the two tables log_shipping_monitor_primary and log_shipping_monitor_secondary in the msdb database and then find out the thresholds. If the backup or restore operations have not been successfully completed in the defined threshold period, it then generates the alert.
I will explain on the different alerts that we receive for log shipping and what they mean in another post.