<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 High Availability
»
SQL Log Shipping out of sync - Comparing Log...
19 posts, Page 1 of 2
1
2
»»
SQL Log Shipping out of sync - Comparing Log shipping Primary and Secondary Servers
Rate Topic
Display Mode
Topic Options
Author
Message
SqlSWAT
SqlSWAT
Posted Tuesday, January 29, 2013 12:42 PM
SSC Rookie
Group: General Forum Members
Last Login: Monday, May 20, 2013 11:31 AM
Points: 38,
Visits: 336
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
Perry Whittle
Perry Whittle
Posted Thursday, January 31, 2013 9:11 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 5,204,
Visits: 11,158
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
george sibbald
george sibbald
Posted Thursday, January 31, 2013 9:52 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 5,269,
Visits: 11,206
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
SqlSWAT
SqlSWAT
Posted Wednesday, February 06, 2013 9:15 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, May 20, 2013 11:31 AM
Points: 38,
Visits: 336
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
Perry Whittle
Perry Whittle
Posted Wednesday, February 06, 2013 4:01 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 5,204,
Visits: 11,158
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
SqlSWAT
SqlSWAT
Posted Thursday, February 07, 2013 8:39 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, May 20, 2013 11:31 AM
Points: 38,
Visits: 336
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
george sibbald
george sibbald
Posted Thursday, February 07, 2013 8:59 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 5,269,
Visits: 11,206
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
Perry Whittle
Perry Whittle
Posted Thursday, February 07, 2013 9:06 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 5,204,
Visits: 11,158
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
SqlSWAT
SqlSWAT
Posted Thursday, February 07, 2013 9:56 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, May 20, 2013 11:31 AM
Points: 38,
Visits: 336
Very much appreciate George and Perry.
Post #1417207
SqlSWAT
SqlSWAT
Posted Thursday, February 07, 2013 10:13 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, May 20, 2013 11:31 AM
Points: 38,
Visits: 336
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 »
19 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
