SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Log Shipping out of sync - Comparing Log shipping Primary and Secondary Servers


SQL Log Shipping out of sync - Comparing Log shipping Primary and Secondary Servers

Author
Message
SqlSWAT
SqlSWAT
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 454
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..
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53896 Visits: 17679
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" ;-)
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24572 Visits: 13698
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

---------------------------------------------------------------------
SqlSWAT
SqlSWAT
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 454
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.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53896 Visits: 17679
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" ;-)
SqlSWAT
SqlSWAT
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 454
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...
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24572 Visits: 13698
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.

---------------------------------------------------------------------
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53896 Visits: 17679
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" ;-)
SqlSWAT
SqlSWAT
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 454
Very much appreciate George and Perry.
SqlSWAT
SqlSWAT
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 454
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 ?
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