Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 Corrupt SMALL transaction log backups in Log Shipp

Author  Topic 

ferSV
Starting Member

1 Post

Posted - 2013-07-05 : 01:10:08
Hello, I have been reading this forum for years and this is my first post.

I have a log shipping scenario set up in my LAN. I have multiple source servers and one backup server for all the restored standby databases. I have problems with the log shipping of some of the databases, specifically the databases that stay without any transaction in some point during the day.

I will explain one of the scenarios but it is the same situation for all the failing databases.
• The source database is Microsoft SQL Server 2008 R2 (RTM) running on Windows Server 2012
• The destination database is Microsoft SQL Server 2008 R2 (SP1) wunning on Windows server 2008

The problem is:
• The log shipping start and keeps working for hours
• Really late a night (when the daabase is not in use) the log shipping fails on the restoring job with the following error, and the database got on supect state:

Msg 9004, Level 16, State 6, Line 3
An error occurred while processing the log for database 'smsGateway'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.


• My first idea was that the logs backups were corrupted while copied over the network, so I recopied the corrupted backup from the source and restored the original full backup, then restored all the log backups manually
• When I arrived to the problematic log backup I got the same error message
The weird thing is that I issue a EXEC sp_resetstatus [smsGateway]; command and the restore the next LOG backup. Incredibly the restore process was successfully when skipping the failing backup.
• I kept restoring the rest of the log backups until the last one that I had generated by the log shipping job.
• Restart the log shipping Jobs and log shipping kept working perfectly

I repeated the above process with all the databases and got the same result.
So my questions and observations:
• How was it possible to just skip the failing log backup and continue the restore process without issues?
I have also notice that this faling backups are only present when database are backed up when they are NOT in use. In other words only backups that are really small in size failed.

In conclusion I think that the log shipping fails when multiple backups are made during a period of time that the database is not in use.
Have you ever hear something like this before? Do you have any other ideas what could be the problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-07-05 : 16:36:12
Have you considered hardware failure? A database going into suspect mode is not normal even if tlog backup files are corrupt.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ysivaram
Starting Member

1 Post

Posted - 2013-08-15 : 04:15:08
Interestingly we also have the same problem. The only difference is our database does not go to suspect state.

By any chance does your target server running on Hyper-V and virtual disks? Ours is having this configuration and I suspect whether it is causing the problem.

Also when restoring the first log (which fails) do you use CONTINUE_AFTER_ERROR parameter? I suspect this is the reason why it allows the subsequent file to be restored?

Did you have any luck in resolving this issue?
Go to Top of Page
   

- Advertisement -