SQL Server Performance Forum – Threads Archive

Restore failed

This job for log shipping is scheduled and its failing EXECUTE msdb.dbo.sp_log_shipping_monitor_restore This is the error description
The job failed. The Job was invoked by Schedule 19 (Log Shipping Alert Job – Restore). The last step to run was step 1 (Log Shipping Alert Job – Restore).
Can pleeeease somebody help me out with this asap

The restore on the secondary server is failing. Please help me out
Rajesh

Its erroring out with error 14421 is out of sync

Have a look :
http://support.microsoft.com/default.aspx?scid=kb;en-us;329133 Troubleshooting Error Message 14421
By definition, message 14421 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. Panic, Chaos, Disorder … my work here is done –unknown

On the secondary server event log its erroring out saying
18204:
Backupdiskfile::’UNC path’ failed to open. Operating system error = 32.(the process cannot handle the file because it is being used by another process). Can somebody tell me how to see which file is locking the backup file and how to resolve this error.
Thanks for replying
Rajesh

Best guess is your backup is still being written…. If that is true, naturally, you are going to have to wait <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> If you suspect this to be the case, check your log’s on source SQL server… I haven’t found a good way to see if a file is in use, from SQL. A couple of "ugly" ways to do this are: Try and rename the file. If the rename fails, then the file is still in use. I will typically do this only if I am really desperate… There is a utility called handle.exe – I can’t really recall who wrote it, but it will tell you who is using the file. Panic, Chaos, Disorder … my work here is done –unknown

Hi,
If I want to isolate the secondary server from primary server in logshipping architecture what is way other than deleting log shipping.

Can you check SQLAgent privileges on secondary server where the restore is failing to open the specified path. Why do you want to isolate secondary server and if you’re deleting log shipping then there is no point of having secondary server. 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.

Consider describing your usage of the Log Shipping server… I get the feeling you are not necessarily using it for Recoverability, but potentially also using it for SQL queries, etc.? Yes/No? Panic, Chaos, Disorder … my work here is done –unknown

Thank you all for replying. The problem was there were lot of 300,000 backup files in msdb created due to log shipping backup restore happening every 15 minutes. Since the SQLEM has to scan all the backup files it used to hung in between. I executed sp_delete_backuphistory and cleared all the backup files and its working now.
Thanks again Satya and SQL GUESS for replying
Rajesh Raghavan

Glad to hear the solution and you should be able to set a job to clearout the history or setting in SQLAgent for future. 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.

Yow – 300K?! that’s .. impressive! [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] Panic, Chaos, Disorder … my work here is done –unknown

]]>