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 Thursday, February 7, 2013 10:22 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 5,872, Visits: 12,974
well thats out of left field.

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


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

Post #1417227
Posted Thursday, February 7, 2013 12:06 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:20 AM
Points: 38, Visits: 432
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.
Post #1417289
Posted Thursday, February 7, 2013 12:49 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:20 AM
Points: 38, Visits: 432
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.
Post #1417321
Posted Thursday, February 7, 2013 1:37 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 5,872, Visits: 12,974
thanks

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

Post #1417340
Posted Friday, February 8, 2013 3:02 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:20 AM
Points: 38, Visits: 432
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.


Post #1417918
Posted Monday, February 11, 2013 11:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:22 AM
Points: 3, Visits: 83
There is bug in SqlLogShip.exe
use msdb
update dbo.log_shipping_secondary_databases set [restore_delay] = -2000000000

Post #1418757
Posted Tuesday, February 12, 2013 2:57 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 5,872, Visits: 12,974
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.


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

Post #1418821
Posted Tuesday, February 12, 2013 4:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 3:22 AM
Points: 3, Visits: 83
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
Post #1418881
Posted Tuesday, February 12, 2013 5:05 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:01 PM
Points: 5,872, Visits: 12,974
not quite the problem we had here (we had log backups taken outside of the log shipping jobs) but thanks for the post.

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

Post #1418886
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse