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


Logshipping out of sync


Logshipping out of sync

Author
Message
Satish-1028188
Satish-1028188
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 127
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 :crazySmile 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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8831 Visits: 16579
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 :crazySmile 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
Sudhakar Vallamsetty
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 77
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
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8831 Visits: 16579
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
Satish-1028188
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 127
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
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8831 Visits: 16579
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
chandan_jha18
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 2133
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
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