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
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10334 Visits: 13687
well thats out of left field.

I don't do replication but I would have thought you would have to reinitialise it from snapshot.

---------------------------------------------------------------------
SqlSWAT
SqlSWAT
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 454
Ok, Guys the The Restore was successful and the Log shipping is back on sync. I can confirm it by looking at the restore log on the Log File Viewer.
It says that the Restore Operation was successful.

I have a question though. The database is now on "Restoring Mode" since we used "NO RECOVERY"
Any idea how to bring it back to Standby Mode since we want to use this for Read purposes ?
thanks.
SqlSWAT
SqlSWAT
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 454
I have restored the last transactional Log backup with the option of STANDBY on SSMS. The database is now back to ReadOnly / STANDBY. Now I users will be able to read from this database and the Log shipping is IN-SYNC.
thanks for all the help guys.
Appreciate it. I will put detailed steps here shortly in case anyone needs it later.
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10334 Visits: 13687
thanks

---------------------------------------------------------------------
SqlSWAT
SqlSWAT
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 Visits: 454
Hi again,
Recently I found two of the databases (Primary and Secondary) on my log shipping were Out Of Sync.
Below are the steps I performed to bring it back to Sync:

1. Identify the SQL Agent Job on the Primary Database server which is taking Transactional Log Backups of your Database and Disable it. ( This is a very critical step to take in a 24X7 environment , So I would recommend this process to be performed on a non-busy period ; like midnight or so. So that you loose minimum data.)

2. Identify the SQL Agent Jobs on the Secondary Database server that are Copying and Restoring Log Backups. Disable them.

3. After Disabling the Agent jobs, Take a Full Backup of the Primary database and Restore it on the Secondary Database, option WITH NO RECOVERY mode. You won't be able to do this in SSMS GUI since the Secondary database is on a STANDBY/READ-ONLY mode and the RESTORE DATABASE options are disabled on the tasks>Restore . USE the T-SQL code instead to perform this step .
( Even a recent Differential backups is enough to perform this Restore if a Full backup has not been taken after the LS OFF-Sync )

4. After a fresh Restore has been performed on the Secondary Database, Execute sp_who2 and see if there are any users that are connected to the Secondary Database. Kill the SPIDs connecting to the Database if there are any.
Don't have to do this if you have selected "Disconnect users in the database when restoring backups" while you setup the Log Shipping.

5. Re-Enable the SQL Agent Job on the Primary Server that was taking Transaction Log Backup. Right click and click "Start Job At Step".

6. Clear all transactional log files (trn) from the Secondary server's Restore Folder.

7. Re-Enable SQL Agent Jobs on the Secondary Database server for Copying and Restoring Log Backups. Right click on the Copy job and click "Start job at Step".
Wait for the .trn files to be copied to the Restore Folder.
After that, Right click on the Restore Job and click "Start Job at Step".

8. You may want to manually restore the last transactional backup file to the Secondary Database with the option (RESTORE WITH STANDBY) on SSMS.

Log shipping should now be back IN-SYNC and ready to READ by users. Use Log File Viewer on the Secondary Server to confirm that Restores are Not Failing.
Look on the "Message" on the Log file summary , It should say something like this; "The restore operation was successful. Secondary Database ....... Number of log backup files restored: ##some number "

You may see some latency between the Live Database and the Secondary Database.


May not apply to all environments.

==============================
Some Info:
Primary: SQL SERVER 2008 R2 Enterprise SP2
Secondary: SQL SERVER 2008 R2 Enterprise SP2
Secondary Database mode: STANDBY/READ-ONLY
Transactional Log Backup: Every 30 mins interval . This is the only log backup taken for the whole database. Running two parallel log backups at same time , BAD IDEA !!
Database size: 400GB
Total Time taken : 1 hour.
hiren-411325
hiren-411325
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 84
There is bug in SqlLogShip.exe
use msdb
update dbo.log_shipping_secondary_databases set [restore_delay] = -2000000000
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10334 Visits: 13687
hiren-411325 (2/11/2013)
There is bug in SqlLogShip.exe
use msdb
update dbo.log_shipping_secondary_databases set [restore_delay] = -2000000000



I've not come across this, in which version? Under what circumstances would you want to run this and what is its effect? Please explain further.

---------------------------------------------------------------------
hiren-411325
hiren-411325
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 84
i have same problem and its work after execute this query on
secondary server

follow this link

http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/44840af1-0a85-44a9-98a6-8fc2ca559f81
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10334 Visits: 13687
not quite the problem we had here (we had log backups taken outside of the log shipping jobs) but thanks for the post.

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