Monday, January 24, 2011

Logshipping Error: "Skipping log backup file" and "Could not apply log backup file"

This was a bad day for log shipping. I was on a leave for last two days and got my junior DBA screeming over logshipping latency as soon I stepped in.

DBA got many skip messages in logshipping as follows:

2011-01-24 21:50:11.17 Skipping log backup file 'j:\logshipping\Tuitionaffordable\Tuitionaffordable_20110123100410.trn' for secondary database 'Tuitionaffordable' because the file could not be verified.

Another interesting error was as follows:

2011-01-24 21:50:10.92 *** Error: Could not apply log backup file 'j:\logshipping\Tuitionaffordable\Tuitionaffordable_20110123100410.trn' to secondary database 'Tuitionaffordable'.(Microsoft.SqlServer.Management.LogShipping) ***
2011-01-24 21:50:10.92 *** Error: Cannot open backup device 'j:\logshipping\Tuitionaffordable\Tuitionaffordable_20110123100410.trn'. Operating system error 32(The process cannot access the file because it is being used by another process.).
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Let me discuss about these errors one by one.

Error 1 indicates that some transactional log file is missed in between. It just means that log chain is broken and you need to copy those logs manually to the secondary server.

Error 2 indicates that the log file is corrupt. Let's run following command to verify the log file.

FROM DISK = 'J:\logshipping\Tuitionaffordable\bak\Tuitionaffordable_20110123100410.trn'

Msg 3203, Level 16, State 1, Line 1
Read on "J:\logshipping\Tuitionaffordable\bak\Tuitionaffordable_20110123100410.trn" failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

And this told that your log file is invalid. This happens when you use robocopy or xcopy to transfer file manually and your session is logged off. So get those files again from primary DB manually. I prefer to create a job and run it so that session logoff doesn't cause any problem.

Most of the readers of this blog also read following interview questions so linking them here:
Powershell Interview Questions and Answers
SQL Server DBA “Interview Questions And Answers”
SQL Azure Interview Questions and Answers Part - 1


No comments:

Post a Comment