SQL Server Performance Forum – Threads Archive

Suspend log shipping schedule

I have a database that I have been using log shipping on sucessfully for almost a year. The database is around 30 GB in size and I had been shipping logs once an hour. Recently, the log shipping restores on the destination server began failing with the following error: [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 25838000001493500001, which is too late to apply to the database. An earlier log backup that includes LSN 25838000001411500001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally. I reconfigure log shipping and it runs for a few days then fails again. I can’t correlate the time of the failures to anything specific on the target server. I have tried to reconfigure log shipping with both more and less frequent log dumps but I keep having this problem. Can anyone offer any suggestions on how to run this problem down? Is there a way to configure log shipping with a variable schedule (assuming that I can find a time window to make this work)? Any ideas would be helpful. Thanks, Chris

Check log shipping monitor about the execution schedule of backup and restore of log.
Ensure there is no network issue between primary and secondary servers while transferring the files during log shipping. BTW what is the log shipping schedule interval? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

As far as we can tell there are no network issues. I have tried intervals at 15 min, 1 hour and 4 hours. I may try once per day to see if that works. It may be possible that another process is running against a different database on the secondary server overnight and that could be causing contention but there is nothing running against my database during that time. Can I somehow interrupt/suspend the log shipping schedule so that it doesn’t run during my production window at night?

Can you check that other process on secondary server and try to avoid clashing of log shipping.
Simply disable and re-enable jobs using another job with sp_update_job, review information from Books oNline. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Just to close out the thread…I figured out our problem. Unfortunately it ends up being a dumb reason that I should have thought of before… I ran some queries against the backupset table in msdb to see if I could find any reason why SQL was giving me the "out of sequence" error when trying to restore the log backups. What I noticed was that there were log dumps that I couldn’t account for from log shipping. The first thing I noticed was that the user ID was not the SQL service account. It turns out that our tech support team (who is responsible for backups of the servers) had accidentally turned on a log dump of the database in question as part of our nightly backup routine through our enterprise backup software. The extra log dump each night was throwing log shipping out of sync. Moral of the story: If something that has been working for a while quits working and you think that nothing has changed, check again.

Absolutely and never allow too many admins to interrupt a smooth going process, if you have a third party software to take care of backups then ensure it doesn’t overlap SQL Server owned process and jobs. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>