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