April 10, 2014 at 9:31 am
Hi all,
We've been trying to get the log shipping of one DB to work for a while now, but it seems like it always crashes between 2am and 2:30am...
It's a log shipping setup is straight forward.
Transaction logs backup happen every 15 minutes (we tried to a local drive and to a network drive - with same results)
on the secondary server the LS copy runs every 15 minutes but with a + 5 minute offset.
and the LS restore runs every 15 with another 5 minute offset.
Everything runs fine until aroud 2am when the message we receive is: Restore failed for Server 'XXX'. System.Data.SqlClient.SqlError: The log in this backup set begins at LSN 42118000000017100001, which is too recent to apply to the database. An earlier log backup that includes LSN 42118000000016900001 can be restored. (Microsoft.SqlServer.Smo)
When we manually restore the transaction log before the one that is in error, it restores without any problem.
we follow with the transaction log file that gave us the error... and still gives us the same error...
Logically, I see 2 possible issues:
1- the pointer on the db changes?
2- The file gets corrupted?
More info:
- We delete copied files over 2 hours.
- we're in standby mode
- we disconnect users when restoring.
If you need more info, feel free to ask.
I'm not sure what else to look at...
any help would be greatly appreciated.
Thanks
JG
April 10, 2014 at 9:41 am
Have you got some kind of VM backup job running every day at around that time?
April 10, 2014 at 9:45 am
... alternatively, have you got a scheduled maintenance plan (that takes a log backup) running at around that time each day?
April 10, 2014 at 9:59 am
VM Backup is very possible... I'll find out from our network admistrators...
As for maintenance jobs, we checked... they don't run at that time
(we do run a full backup (copy only) at 12:30am)
Will let you know
Thanks
JG
April 10, 2014 at 10:54 am
Hi Beatrix Kiddo,
Yes the transaction log backup that fails is the one that is taken during the VM backup.
We will find out a time range that the VM backup is taken, and make sure we pause the log shipping during those times...
Is that how we should proceed to solve the issue?!?!
I'm just surprised on how fast :w00t: you pointed out the problem?
Very impressed 😉 :hehe:
Thank you
JG
April 11, 2014 at 7:42 am
Ok, cool 🙂 . You don't need to pause log-shipping, you just need to get your network/systems guys to make sure they change the settings in Veeam (or whatever they're using) so that it does not truncate the log. I'm not an expert in this area but if you Google for things like 'log-shipping broken by Veeam' there are quite a few resources out there. Or see here, about half way down the page.
Transaction Log Clearing?
For databases that use the full or bulk_logged recovery models, transaction log clearing only occurs when the log is backed up using SQL Server native backup commands. The VSS backup will not cause log clearing to occur inside of SQL Server on it’s own. Some of the VM backup tools offer options to perform transaction log clearing, but you need to be very careful with these options, especially if you have concurrent SQL Server backups being performed for the VM. The way that certain tools clear the transaction log during a VM snapshot backup is to issue a subsequent command to the SQL Server, BACKUP LOG <database_name> TO DISK =’NUL’, which dumps the transaction log records to nowhere, essentially throwing them away completely and breaking the log backup chain for any native SQL Server backups being performed. If you are still using native SQL Server backups, it is recommended that the VM snapshot backups be configured to not truncate the transaction logs for the databases.
April 11, 2014 at 8:16 am
The Network Administrator verified and found the place to change it... We can only wait and see tonight...
Thank you so so much!!! you just saved me many many more headaches.
😉
JG
April 11, 2014 at 8:20 am
Fingers crossed it works!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply