SQL Server 2008 R2 log shipping does not behave as expected

Question

Yesterday I was given an existing SQL Server 2008 R2 to maintain, and I'm seeing some very strange behaviour in its Log Shipping.

It's a 20Gb database (of which 16Gb is the log file), with log shipping set up to run every 5 minutes to two remote sites. Each one of the transaction backup files ranges in size from 600Kb to 20Mb.

However, sometimes the remote sites just get stuck at a certain point for no reason that I can fathom. Right now the data in the remote sites is 18 hours old (confirmed this by running a SELECT query that has a new record inserted every 60 seconds), but according to all the logs everything is working perfectly.

The primary database generates the transaction logs every 15 minutes, they are copied to the remote sites and (allegedly) restored. Both databases are stuck at the exact same moment in time.

Given the size of the database and the bandwidth of the WAN links I'd rather not re-initiate the log shipping if possible. Any ideas what could be causing this?

Answer 1

Is the log shipping configured to delay the application of the logs?

If you check the Log Shipping setup, in the Secondary Database Settings there is a Restore Transaction Log tab. This contains a field called Delay Restoring Backups at least. Check that this is not set to something like 18 hours.

You mention that the logs are restored - allegedly. You should see messages related to these restores completing in the SQL Server Error log.