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.

RESTORE VERIFYONLY
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



Regards,
http://tuitionaffordable.webstarts.com

1 comment:

  1. Step by step instructions to Solve MS SQL Server Error 3203 through Online MS SQL Server Support
    Confronting mistake 3203 in SQL Server? Or on the other hand can't get any sort of help to fathom this blunder message? Well! Try not to freeze, basically contact to Microsoft SQL Server Support and SQL Server Database Support to take care of this issue. In any case, before that you should know why this issue happens? The primary purpose for this issue is if arrange disappointment happens, in the event that you are utilizing Microsoft SQL Server 2014. Contact to Remote Infrastructure Management Support for Microsoft SQL Server.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete