log shipping failure

Posted on 2007-07-23
Medium Priority
Last Modified: 2008-01-09
Hi experts, How can I get to know the reason why a alert job failed at certain point. It fails a few times that day.

Log Shipping Alert Job - Restore Log Shipping Alert Job - Restore
Run date/time - 20070721/03:25:03
Run duration in hours:minutes:seconds - 00:00:01
Executed as user: PVIEW2001\SQLAdmin. The log shipping destination PVIEWS2002\PVIEW.CUSTDB is out of sync by 70 minutes. [SQLSTATE 42000] (Error 14421).  The step failed.

and after that went to normal? I am assuming it went back to normal because no more messages was received.
Question by:sharscho
  • 4
  • 3
LVL 14

Expert Comment

ID: 19546127

Author Comment

ID: 19547457
Hi thanks for the link you gave. I saw that the error does not indicate a problem with the logshipping. I looked on the MS site and then went to look in the event viewer of the secondary server. I see the following error for the logs.
18204 :
BackupDiskFile::OpenMedia: Backup device 'D:\SQLServer_Backups\transaction_logs\Impact_tlog_200707230105.TRN' failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.).

the last time it happened was at 1 this morning. and it started since after that it did not happen.
Is it possible that it has something to do with the backup of the db? I see that only the system dbs are being backed up since this is the failover db. but the backup is done in 2 min. and so I don't think it is.

Do you have more sugestions?
LVL 14

Expert Comment

ID: 19547493
Is it possible that the file was being written to by the primary, whilst the secondary was trying to read it?
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.


Author Comment

ID: 19547776
How do I get to know that? Normally the log is shipped to the secondary server and depending on the network speed it might take longer than normal. Now in the patern I see that between 8pm and 3 in the morning thsi error occurs, every day, except yesterday. where can I get more info about this log shipping/replication process?
LVL 14

Expert Comment

ID: 19547890
Have a look in the primary server logs to see what time the log backup for that particular db finished. You should be able to compare that time with when the error occurred. (Don't forget to allow for any system time differences between the servers).

This is the usual resource material for log shipping


Author Comment

ID: 19581201
Hi experts, I did read some things in the articles of the link you have send and I did look at the  log_shipping_plan_history table on the secondary server where I queried the rows with succeeded of 0. There I saw the error again of [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'D:\SQLServer_Backups\transaction_logs\Impact_tlog_200707260105.TRN'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

Now the only conclusion I can get from that is that sqlserver on the secondary tried to apply the trn logs while the file copying was still going on.

I queried also to see if the trn log had more entries and I got 4 entries 2 with succeeded 0 and 2 with succeeded 1. so the log file was applied after a couple of tries. Is this the way it goes??
But the timings are strange:
7/26/2007 1:10:08 AM -->Succeeded 0 -Activity 1
7/26/2007 1:15:08 AM -->Succeeded 0 -Activity 1
7/26/2007 1:05:02 AM -->Succeeded 1 -Activity 0
7/26/2007 1:05:07 AM -->Succeeded 1 -Activity 1

If I can get some explanation of this occurances, it would be appreciated.
I also want to confirm if my assumptions about the log shipping copy and apply are correct.
LVL 14

Accepted Solution

twoboats earned 750 total points
ID: 19584100
"Now the only conclusion I can get from that is that sqlserver on the secondary tried to apply the trn logs while the file copying was still going on."

Yes, I reckon so too. Try extending the delay between the copy and the load.


Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question