x

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.

more ▼

asked Nov 05, 2009 at 08:01 AM in Default

lark gravatar image

lark
12 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Nov 05, 2009 at 09:14 AM

John Sansom gravatar image

John Sansom
897 2

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 06, 2009 at 11:35 AM

lark gravatar image

lark
12 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x64

asked: Nov 05, 2009 at 08:01 AM

Seen: 3269 times

Last Updated: Nov 05, 2009 at 09:32 AM