Logshipping out of sync

Question

Logshipping out of sync

Satish-1028188

Mr or Mrs. 500

Points: 588
More actions
September 18, 2012 at 4:28 am

#264300

Guys,
I have logshipping configured in SQL Server 2008 boxes, some times this goes out of sync for some of databases (few of them are quite large in size) due to LSN mismatch, is there any way to re-sync the logshipping without reconfiguring it through wizard?
My full backups are running over night, even then it never picks them up autometically, to restore them in secondary to re-sync the logshipping. Everytime I need to reconfigure the logshipping through configuration wizard through taking new full backup to re-sync (then it works fine till next failure :crazy:) which impacts my backup strategy big time, some of my databases are quite big in size, which makes it even worse...
Am I missing on any setting or something...??
let me know you if you need more details.
Thanks
~Satish

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

Satish-1028188 (9/18/2012)
Guys,
I have logshipping configured in SQL Server 2008 boxes, some times this goes out of sync for some of databases (few of them are quite large in size) due to LSN mismatch, is there any way to re-sync the logshipping without reconfiguring it through wizard?
My full backups are running over night, even then it never picks them up autometically, to restore them in secondary to re-sync the logshipping. Everytime I need to reconfigure the logshipping through configuration wizard through taking new full backup to re-sync (then it works fine till next failure :crazy:) which impacts my backup strategy big time, some of my databases are quite big in size, which makes it even worse...
Am I missing on any setting or something...??
let me know you if you need more details.
Thanks
~Satish

Firstly check the LS jobs, especially the restore job carefully for any errors.

Check you do not have any log backups occuring against the Log Shipped databases outside of the LS backup job.

You can resume LS from a differential backup, see my article at this link.

it would be nice to get an idea of the schedules and types of your backup jobs and the point at which they break

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Sudhakar Vallamsetty SSC-Addicted Points: 407 More actions · Answer 2

As Perry told, it is good to troubleshoot why in first place the LS restore job is not picking up the t-log backups.

There is no need to re-configure LS each time on failure. Get the last successfully restored t-log from LS tables present in MSDB database and apply subsequent t-log backups from Primary server on Secondary server(make sure you disable the LS restore job while doing)

*******:cool:
Sudhakar

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

Sudhakar Vallamsetty (9/18/2012)
Get the last successfully restored t-log from LS tables present in MSDB database and apply subsequent t-log backups from Primary server on Secondary server(make sure you disable the LS restore job while doing)

If these are missing then this won't work. Also, even if they do exist it can be a real pain to start restoring lots and lots of logs, a differential backup from the primary restored to the secondary is quick and easy and will bridge any LSN gaps 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Satish-1028188 Mr or Mrs. 500 Points: 588 More actions · Answer 4

Thanks Perry, your suggestions make sense, I would see now, How I can impliment this in my case.

Thanks Sudhakar, yes (in last few cases) it was in-between t-log backups which caused these failure, I am digging more to find if there were any other causes...

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

Satish-1028188 (9/18/2012)
Thanks Perry, your suggestions make sense, I would see now, How I can impliment this in my case.
Thanks Sudhakar, yes (in last few cases) it was in-between t-log backups which caused these failure, I am digging more to find if there were any other causes...

need to know a bit more about your jobs and their schedules

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

chandan_jha18 SSChampion Points: 11330 More actions · Answer 6

Satish-1028188 (9/18/2012)
Thanks Perry, your suggestions make sense, I would see now, How I can impliment this in my case.
Thanks Sudhakar, yes (in last few cases) it was in-between t-log backups which caused these failure, I am digging more to find if there were any other causes...

As Perry mentioned, make sure that the log backups are defined by Log shipping schedule. You should monitor the msdb backup tables to know how the backups are running. It should be only done by the sql agent. If a user is taking a backup, it is going to break the LSN chains. also make sure that the cleanup jobs run only when logs are shipped successfully.

I would also go to each log shipping job manually and make it write the output to a report file and I would like to append the data each time the job runs to keep a track of behavioral patters of failures.

Chandan