Log Shipping Alerts – What do they actually mean?

In my previous post I explained about the Log Shipping Alert Job in SQL Server. In this post I will explain on the different alerts and what they mean. I have seen multiple questions raised on this in the forums.

Below are the different types of alerts that I have seen with log shipping.

Alert  1

From: Ashwin [mailto:ashwin.menon@rediffmail.com]
Sent: 05 April 2012 10:28
To: Ashwin Menon
Subject: SQL Server Alert System: ‘Log shipping Primary Server Alert.’ occurred on \\MyPC\R2

DATE/TIME: 05/04/2012 10:28:00

DESCRIPTION: The log shipping primary database MyPC\R2.LSPrimary has backup threshold of 30 minutes and has not performed a backup log operation for 43 minutes. Check agent log and logshipping monitor information.
COMMENT: (None)

JOB RUN: (None)

What does this alert means?  It is one straight forward  alert that you get in log shipping. It clearly explains that in the primary server MyPC\R2 the transaction log backups of LSPrimary database has not happened. We have a threshold configured for 30 minutes and the alert is raised because in last 43 minutes there was no backup operation.

Now what should we do? It is so simple. Just check the backup job in the primary server and see if it ran, if it didn’t find the cause for that. If it ran and if it failed, then resolving that would stop the alert from being issued.

Alert 2

From: Ashwin [mailto:ashwin.menon@rediffmail.com]
Sent: 05 April 2012 10:48
To: Ashwin Menon
Subject: SQL Server Alert System: ‘Log shipping Secondary Server Alert.’ occurred on \\MyPC\R2

DATE/TIME: 05/04/2012 10:48:00

DESCRIPTION: The log shipping secondary database MyPC\R2.LSSecondary has restore threshold of 35 minutes and is out of sync. No restore was performed for 63 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
COMMENT: (None)

JOB RUN: (None)

What does this alert means? This alert means that in the instance MyPC\R2 there is this database LSSecondary and a restore of the transaction log has not occurred in the last 63 minutes. The restore threshold for this database was set to 35 minutes and hence it is now out of sync.

What do we do now? This alert can be caused by two reasons

a) Due to the copy job

Check the copy job and see whether it ran as per schedule. If it hasn’t run in schedule then the restore job would not have got the last transaction log file to restore and hence the reason for this error. Fix the issue causing the job not to run in schedule.

If it ran as per schedule, Check the copy job and see whether it failed in its last run. If it has failed in the last run then it would also cause the same as above. Fix the issue causing it to fail.

b) Due to restore Job :

If it is not due to the copy job and if the latest transaction log files are available in the backup folder in the secondary server then the restore job could be the reason for this alert. The restore job didn’t run in schedule or it failed in the last run. Fixing the restore job issue will help resolving the alert.

Alert 3

From: Ashwin [mailto:ashwin.menon@rediffmail.com]
Sent: 05 April 2012 12:16
To: Ashwin Menon
Subject: SQL Server Alert System: ‘Log shipping Secondary Server Alert.’ occurred on \\MyPC\R2

DATE/TIME: 05/04/2012 12:16:00

DESCRIPTION: The log shipping secondary database MyPC\R2.LSSecondary has restore threshold of 20 minutes and is out of sync. No restore was performed for 11 minutes. Restored latency is 25 minutes. Check agent log and logshipping monitor information.
COMMENT: (None)

JOB RUN: (None)

What does this alert means? This alert is the most confusing one in the lot. This alert says that the instance MyPC\R2 has a database LSSecondary which has no restore operation performed for last 11 minutes. It also says that it has a restore threshold of 20 minutes and is out of sync.

Now this raises question as to if the last restore was indeed within the threshold of 20 minutes then why the alert. Well it also tells us that the Restored Latency is 25 minutes. What does it mean? It means that the last backup that was restored was backed up 25 minutes before and hence LS is out of sync as the restore threshold is 20 minutes. So the restored latency is the time elapsed since the last backup was completed in the primary server and restored. So for this alert not to occur the backups taken at the primary should be copied to the secondary server and restored within the restore threshold.

Now what do we do? We need to do the same thing that we did for the previous two alerts as the reason here can be both due any of the backup/copy/restore job not running in schedule/running slow/ failed etc.

If you know any other alert that would be raised in log shipping or if you feel that there is more towards this please feel free to comment. I hope this would give you some information to at-least start troubleshooting if you get any log shipping alerts

2 thoughts on “Log Shipping Alerts – What do they actually mean?

  1. Hi Ashwin,

    could you help me to create the alerts for application owners. please guide me to create the mail alerts step by step. am awaiting for your reply.

    Thanks in advance.

    could you reply me on kgayathri.cs@gmail.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s