Log Shipping 'skipping' all logs and not restoring

After you configured log shipping in SQL Server, you verify and saw all the jobs are working fine: LSBackup, LSCopy and LSRestore.  But after several hours you saw a message indicating "log shipping is out of Sync". 

When you go and review the job history all of them indicate were running fine, but in the restore job you found something like this:

"Skipped Log Backup File ....."
"Could not find a log backup file that could be applied to secondary Database"

It happen for an old issue reported since version 2000 and still happen in the new SQL Server version 2008.

To force the sync you can run the following queries:

1. You need to check the current configuration in the secondary node.

--This query will show you the current restore delay per database

Select secondary_database, restore_delay from dbo.log_shipping_secondary_databases

secondary_database                   restore_delay

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

Adventures                               360

Testing                                    360

2. After you have the current values run the following query in msdb.

use [msdb];

update

dbo.log_shipping_secondary_databases

set [restore_delay] = -2000000000

3. Start LSRestore job.

You will see the report with sync information after some minutes; please keep in mind, all depends of the size of your database if you are restoring large transaction log files it can take hours.

4. Finally return the values to the previous values with the following query:

use [msdb];

update

dbo.log_shipping_secondary_databases

set [restore_delay] = 360

Any question please contact me at: sqlexpertsrucs@gmail.com