Log Shipping - LSRestore Job Failing

Hi,

I've setup log shipping for a few databases on our SQL 2005 production cluster (SP2). After a few hours 50% of the databases being replicated start to fail during the LSRestore job on the secondary server with the below error;

Message
2009-11-05 10:45:00.92  *** Error: The file 'j:\backups\logshiplogs\DB_NAME_20091104160500.trn' is too recent to apply to the secondary database ‘DB_NAME’.(Microsoft.SqlServer.Management.LogShipping) ***
2009-11-05 10:45:00.94  *** Error: The log in this backup set begins at LSN 14155000000485900001, which is too recent to apply to the database. An earlier log backup that includes LSN 14155000000452300001 can be restored.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2009-11-05 10:45:00.94  Searching for an older log backup file. Secondary Database: 'DB_NAME'

Now I know that it's complaining because previous logs have not been applied even though they have been copied across to the secondary server.

Could the issue be because we also run transaction log backups every 4 hours and the LSN's chain is being broken?

Any thoughts or recommendations, should I stop backing up the transaction logs on the primary server every 4 hours apart from the Full backup that runs every night? But then again it does not affect all of the databases that are being replicated!

Any help/advise would be appreciated. Thanks in advance.

asked Nov 05 '09 at 08:01 AM in Default

lark
12 ● 1 ● 1 ● 1

(comments are locked)

2 answers:

Taking additional Transaction Log backups will NOT break the log chain, so providided these are being picked up by your "COPY" job (the job that ships your transaction logs from the primary server to the secondary server), log shipping should continue to operate just fine.

Changing the recovery model of a database, from FULL to either SIMPLE or BULK LOGGGED, is typically the cause of a broken log chain.

For further reading see Log Sequence Numbers and Restore Planning

I wonder if perhaps the Restore Job is attempting to restore a Transaction Log Backup against the wrong database. Have you created a seperate folder for each database, to store the transaction log backups on the secondary server? Doing so will ensure that each restore job only has access to the appropriate transaction log backups.

answered Nov 05 '09 at 09:14 AM

John Sansom
897 ● 2

(comments are locked)

John,

Thanks for your reply, I think you may of highlighted the issue,

I don't think the 4 hourly translog backup is being picked up by the log shipping copy job. Is there anyway of forcing the LS copy job to pickup the 4 hourly backup?

Thanks

answered Nov 06 '09 at 11:35 AM

lark
12 ● 1 ● 1 ● 1