Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Corrupt log backups, in log shipping scenario when database is without activity


Corrupt log backups, in log shipping scenario when database is without activity

Author
Message
Fernando Romero
Fernando Romero
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 28
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search