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 12»»

SQL Log Shipping out of sync - Comparing Log shipping Primary and Secondary Servers Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 12:42 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:28 AM
Points: 38, Visits: 431
Hello all,
I have a SQL log-shipping High availability setup from a Primary Server to a Secondary Server which ships transactional logs every 1 hour. Both the databases are running on SQL Server 2008 R2.
According to my understanding every changes that are commited on the Primary Server should reflect on the Secondary Server. Today I ran Redgate SQL Compare to compare the two databases and discovered 38 objects which are inconsistent between the two servers. So I waited for 1 hour for the next restore to be completed on the secondary server. Even after all the log backups have been restored to the Secondary server, the two databases are not identical.
I created a test table on the primary database to see whether it will be created by the restores on the Secondary server, but it is not created even after many restores..

Hourly backup is taken without any errors, and None of the transactional logs on the Secondary server is failing.
Is there any fault on my Log shipping setup ? Where to start troubleshooting ?
What might be the case ? Please suggest..
Post #1413261
Posted Thursday, January 31, 2013 9:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 6,184, Visits: 13,331
sdpages (1/29/2013)
Hello all,
I have a SQL log-shipping High availability setup from a Primary Server to a Secondary Server which ships transactional logs every 1 hour. Both the databases are running on SQL Server 2008 R2.
According to my understanding every changes that are commited on the Primary Server should reflect on the Secondary Server. Today I ran Redgate SQL Compare to compare the two databases and discovered 38 objects which are inconsistent between the two servers. So I waited for 1 hour for the next restore to be completed on the secondary server. Even after all the log backups have been restored to the Secondary server, the two databases are not identical.
I created a test table on the primary database to see whether it will be created by the restores on the Secondary server, but it is not created even after many restores..

Hourly backup is taken without any errors, and None of the transactional logs on the Secondary server is failing.
Is there any fault on my Log shipping setup ? Where to start troubleshooting ?
What might be the case ? Please suggest..

Check the jobs carefully and inspect the output of each step, i'm betting that the restore job is showing errors restoring files. Post back what you find.


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1414196
Posted Thursday, January 31, 2013 9:52 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:21 PM
Points: 5,975, Visits: 12,883
also the restore job will report success even if it did not actually restore any logs because it could not find any with correct LSNs.

It sounds as if your logs are not restoring. Check your sql errorlog for evidence of logs restoring and run this to tell you last log copied and restored

select * from msdb.dbo.log_shipping_monitor_secondary


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

Post #1414231
Posted Wednesday, February 6, 2013 9:15 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:28 AM
Points: 38, Visits: 431
Hey, ya'll are right guys. Looks like the Restore jobs on the Secondary database is not restored successfully.
( Eventhough it shows that job run successfully )
The sql event log shows that the trn files are skipped while the trying to Restore. I am pretty positive that it has to do something with the transactional log sequence number.
The day when I did this log shipping setup there was a log backup job ( our original log backup from the maintenance plans ) still running parallel to our new log shipping transactional log backup . So as soon as I realized that there were two transactional log backups running side by side and interfering with each other's lsn's I disable/deleted the original log backup. But I guess I was too late.

Now, I am pretty positive that deleting all transactional log backups and setting up the log shipping from the beginning will solve this problem.
I will keep it posted...
thanks for all the help.

Post #1416583
Posted Wednesday, February 6, 2013 4:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 6,184, Visits: 13,331
Have you taken any full backups since LS broke, if not you may restart LS with a differential backup

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1416759
Posted Thursday, February 7, 2013 8:39 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:28 AM
Points: 38, Visits: 431
Yes , that's what i thought . But I take Full backups every 3 days and diffs everyday. And the LS is out of sync for like a week now. It may be easier for me to reconfigure the whole log-shipping setup. Doesn't take much time though.
Is there anyway i can restore the last full backup + last diff backup + all the trans log backups to the Secondary log shipped database and put it back to sync.
Right now the database is in Standby/Read only mode.
Appreciate the help...
Post #1417121
Posted Thursday, February 7, 2013 8:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:21 PM
Points: 5,975, Visits: 12,883
BTW, you don't need to reconfigure logshipping you can use your existing jobs and set up, just reinitialise the backup.

You would be best pausing log jobs, taking a full backup (in copy_only mode if you are worried about your diffs) and just restore that to the secondary in norecovery mode, then re-enable your log shipping jobs. Clear down the log directory on the secondary first if you want.


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

Post #1417143
Posted Thursday, February 7, 2013 9:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 6,184, Visits: 13,331
george sibbald (2/7/2013)
BTW, you don't need to reconfigure logshipping you can use your existing jobs and set up, just reinitialise the backup.

You would be best pausing log jobs, taking a full backup (in copy_only mode if you are worried about your diffs) and just restore that to the secondary in norecovery mode, then re-enable your log shipping jobs. Clear down the log directory on the secondary first if you want.

+1


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1417154
Posted Thursday, February 7, 2013 9:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:28 AM
Points: 38, Visits: 431
Very much appreciate George and Perry.
Post #1417207
Posted Thursday, February 7, 2013 10:13 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:28 AM
Points: 38, Visits: 431
Guys,
Is the full restore possible on the Secondary Database while it is being Replicated ?
My secondary database for this log shipping is replicated. So do i have to disable the replication before I restore the Full Backup on this database ?
Post #1417220
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse