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

Logshipping out of sync Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 4:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 3, 2014 3:22 PM
Points: 38, Visits: 125
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 ) 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
Post #1360668
Posted Tuesday, September 18, 2012 4:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
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 ) 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"
Post #1360675
Posted Tuesday, September 18, 2012 5:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 3, 2013 12:38 AM
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)


*******
Sudhakar
Post #1360685
Posted Tuesday, September 18, 2012 5:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
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"
Post #1360694
Posted Tuesday, September 18, 2012 5:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 3, 2014 3:22 PM
Points: 38, Visits: 125
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...
Post #1360695
Posted Tuesday, September 18, 2012 5:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 6,318, Visits: 13,623
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"
Post #1360698
Posted Friday, September 21, 2012 3:42 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:31 PM
Points: 466, Visits: 1,914
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


Post #1362506
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse