Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log Shipping issue that we can't understand Expand / Collapse
Author
Message
Posted Thursday, April 10, 2014 9:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202
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




Post #1560505
Posted Thursday, April 10, 2014 9:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:56 AM
Points: 451, Visits: 1,665
Have you got some kind of VM backup job running every day at around that time?
Post #1560510
Posted Thursday, April 10, 2014 9:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:56 AM
Points: 451, Visits: 1,665
... alternatively, have you got a scheduled maintenance plan (that takes a log backup) running at around that time each day?
Post #1560512
Posted Thursday, April 10, 2014 9:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202
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
Post #1560519
Posted Thursday, April 10, 2014 10:54 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202
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 you pointed out the problem?

Very impressed

Thank you
JG
Post #1560540
Posted Friday, April 11, 2014 7:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:56 AM
Points: 451, Visits: 1,665
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.
Post #1560919
Posted Friday, April 11, 2014 8:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202
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

Post #1560953
Posted Friday, April 11, 2014 8:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:56 AM
Points: 451, Visits: 1,665
Fingers crossed it works!
Post #1560959
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse