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.
| 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 2008The 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 3An 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 3RESTORE 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 perfectlyI 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 |
|
|
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? |
 |
|
|
|
|
|
|
|
Corrupt SMALL transaction log backups in Log Shipp