Logshipping error on primary server

Question

Hi,

We setup logshipping from server a(primary) to server b(standby).The jobs are running fine but we are getting the error meseage :

 

The log shipping secondary database server a.dbname has restore threshold of 45 minutes and is out of sync. No restore was performed for 7659 minutes. Restored latency is 15 minutes. Check agent log and logshipping monitor information.

Why the error messeage like this?

Thanks,

Ron.

Answer 1

What version of SQL Server?  SQL 2005 and earlier do not have indexes on the MSDB tables tracking backup/restore.  A large quantity of backup files combined with a lack of indexes can give this kind of errors.  Another symptom is the RESTORE DATABASE dialog box hanging for a long time while it scans the backup history files.

The solution is to add indexes to MSDB and then trim down the backup history.

---

Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

Answer 2

Thanks for reply

Sqlserver version is sqlserver2008 standard.on which tables can i create indexes on msdb db?

Thanks,

Ron.

Answer 3

SQL 2008 has indexes on MSDB.  Create a maintenance job that trims your backup history information to about 90 days.

---

Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

Answer 4

I'd suggest that you take a look in the 'log_shipping_monitor_secondary' table in the msdb database http://msdn.microsoft.com/en-us/library/ms188004.aspx and check that the entry for the 'last_restored_file' for the database in question looks correct and if not, look into the cause and resolve.

Answer 5

Hi ronsql,

By definition, the error message does not necessarily indicate a problem with Log Shipping. This message indicates that the difference between the last backed up file and last restored file is greater than the time selected for the Out of Sync Alert threshold.

 There are serveral reasons why the alert message is raised. The following list includes some of these reasons:

1. The date or time (or both) on the primary server is modified such that the date or time on the primary server is significantly ahead between consecutive transaction log backups.

2. The log shipping Restore job that is running on the secondary server cannot connect to the monitor server msdb database to update the log_shipping_secondaries table with the correct value. This may be the result of an authentication problem between the secondary server and the monitor server.

3. You may have set an incorrect value for the Out of Sync Alert threshold. Ideally, you must set this value to at least three times the frequency of the slower of the Copy and Restore jobs. If the frequency of the Copy or Restore jobs is modified after log shipping is set up and functional, you must modify the value of the Out of Sync Alert threshold accordingly.

4. Problems either with the Backup job or Copy job are most likely to result in "out of sync" alert messages. If "out of sync" alert messages are raised and if there are no problems with the Backup or the Restore job, check the Copy job for potential problems. Additionally, network connectivity may cause the Copy job to fail.

5. It is also possible that the Restore job on the secondary server is failing. In this case, check the job history for the Restore job because it may indicate a reason for the failure.

A similar thread could help you: http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/9de5102f-1072-44b6-a295-2649a98baa20.

Thanks,
Maggie

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 6

I faced similar issues in logshipping but it's in SQL 2000. Just my 2 cents in case it helps you.

1. Check the server where logshipping monitor is running. Check whether the other server's SQL agent account have atleast read/ write permissions to the MSDB database on the server where logshipping monitor is running. For example, if the log shipping monitor is running on primary server, check whether the secondary server's sql agent service account has atleast read/ write permissions to the primary server's MSDB database.

2. Check whether any of the alert jobs are failing (In SQL 2000, the alert jobs are: Log Shipping ALert Job- Backup, Log Shipping ALert Job - Restore). In our case, the backup/copy/restore jobs are scheduled to run every 30 mins. The alert thresholds for backup failures was originally set to: 45 mins, out of sync alert threshold was originally set to: 45 mins. At times, the restore job used to run behind by 60 mins. Because of this, in logshipping monitor it used to show the status of logshipping as out of sync even though all the logshipping jobs ran successfully. We had adjusted the backup threshold alert to: 90 mins and out of sync threshold to: 180 mins based on the default settings for logshipping in sql 2000 (the default schedule is each of backup/ copy/ restore jobs runs every 15 mins, alert thresholds for backup failures is: 45 mins, alert thresholds for out of sync failures: 90 mins). After the changes have been made for the alert thresholds, the status of logshipping monitor reflected normal.

Hope this helps. 

Regards,

Rocky,SQL DBA

Answer 7

Have you checked the restore jobs are actually doing something.  They will complete successfully even if they don't restore anything, check there and make sure restores are actually happening and they aren't reporting "no files to restore" or similar.

Also check the error logs on both servers, on the secondary you should see the database getting restored to.  Also check for any login failures, I have seen this issue before when a monitor instance couldn't be reached to update the system tables, restores were happening fine but the alerts were firing as they didn't know the restores had taken place.

Cheers

Mat