transaction logs too late to apply

Question

transaction logs too late to apply

DB King

SSC Eights!

Points: 806
More actions
December 5, 2005 at 5:00 am

#90943

log shipping transaction logs too late to apply
Hi
I have been looking all over the web recently and I have not been able to solve this problem. I am trying to setup logshipping but it fails over night after the full backup occurs. I already have a maintenace plan which outlines the complete backup daily and hourly transaction log backup so I didnt want to disturb the original full backup and decided to setup a new one for the log shipping of the transaction log. The error I am getting is like:
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
on the microsoft website (http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part4/c1361.mspx) it states the initial full backup was out of sync. But that cant be the case as the log shipping was working until 2am when the full backup occured!
I have now deleted the maintenance plan for the logshipping but it was a basic 15 min copy and restore immediately. The log shipping monitor showed the current dates for the backup file and copy with a delta less than 15 mins (good) but the load was something like 3000 mins out (a full weekend). The configuration for the main maintenance plan is:
###########
# General #
###########
Plan Name: DB Maintenance Plan - Hats database
Databases: Hats
#################
# Optimizations #
#################
Reorganize data and index pages: check
Change free space per page percentage to: 10 %
Schedule
--------
Occurs every 1 week(s) on Sunday, at 01:00:00.
#############
# Integrity #
#############
Check database integrity: check
Include indexes: selected
Perform these tests before backup up the database or transaction log: check
Schedule
--------
Occurs every 1 week(s) on Sunday, at 00:00:00.
###################
# Complete backup #
###################
Back up the database as part of the maintenance plan: check
verify the integity of the backup upon completeion: check
Disk: selected
use this directory: e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP
create a sub-directory for each database: check
remove files older than: 3 Weeks
backup file extension: BAK
Schedule
--------
Occurs every 1 day(s), at 02:00:00.
##########################
# Transaction Log backup #
##########################
Backup the transaction log of the database as part of the maintenance plan: check
verify the integrity of the backup upon completion: check
Disk: selected
use this directory: e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP
create a sub-directory for each database: check
remove files older than: 3 Weeks
backup file extension: TRN
Schedule
--------
Occurs every 1 day(s), every 1 hour(s) between 00:00:00 and 23:59:59.
#############
# Reporting #
#############
text reports
------------
write report a text file in directory: D:\Program Files\Microsoft SQL Server\MSSQL\LOG
delete text report files older than: 4 Weeks
History on this server
----------------------
write history to the table msdb.dbo.sysdbmaintplan_history: check
Limit rows in the table to: 1000 rows for this plan
WHAT am I doing wrong ?
Regards

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gagandeep Singh SSCommitted Points: 1513 More actions · Answer 1

This problem is by product design. U cannot have two backup plans for the same database - either u choose backups or Log shipping, because they essentially mean the same thing with or without restoring a read-only copy of the backup.

When u take a Full backup SQL Server stores the ending LSNs, which becomes the starting LSN of the next Log backup (if DB is in Full Recovery/Bulk-Logged Model). Lets suppose the LSN is 1000. Then u backup the log after 15 minutes and it starts from 1000 LSN and lets suppose that the log backup ending LSN is 2000. In logshipping the full backup occurs only once and all the remaining backups are Log backups - so a sequence of LSNs is maintained throughout.

But u have another Maint Plan that takes a Full Database Backup of the same database again and now the new LSN becomes 3000 (suppose). SO the next Log backup starts at 3000 instead of 2000, which was supposed to be the starting point. SO when the new log backup ships over to the secondary server, the read-only DB expects LSN 2000 but u r shipping LSN 3000 and hence:

[Microsoft SQL-DMO (ODBC SQLState: 42000)]

Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set

begins at LSN 7000000026200001, which is too late to apply to the database. An earlier

log backup that includes LSN 6000000015100001 can be restored.

[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

Remove your second Maint Plan and Logshipping will start working.

David le Quesne SSCertifiable Points: 5229 More actions · Answer 2

Hi

I have a similar problem. Since I have standard edition I have written my own log shipping routines which work fine all week. Every night there is a full backup followed by a transaction log backup. Only the transaction log is shipped. Then during the working day the transaction logs are backed up and shipped every fifteen minutes.

This works fine until the weekend, when there is a database re-organisation and maintenance job. The transaction log created after this is huge, over 200Mb, (normally they are less than 1Mb), and although the steps which ship the log apparently succeed, it appears that the restore to the target server doesn't complete, because the next log to ship fails with the message.

Executed as user: sa. RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) The log in this backup set begins at LSN 294000007385600001, which is too late to apply to the database. An earlier log backup that includes LSN 290000004039000001 can be restored. [SQLSTATE 42000] (Error 4305). The step failed.

The full database backups do not seem to interfere with the log shipping. I ship logs all week with an unshipped full backup every night without error. I have tried shipping database backups, which are about 700Mb, but they seem to suffer the same problem, the job succeeds but the restore does not complete.

I am wondering, is there some sort of timeout or file size limit on the larger restore jobs which prevents them completing?

david

If it ain't broke, don't fix it...

DB King SSC Eights! Points: 806 More actions · Answer 3

Tryinig to make sense of it all, Gagandeep's solution seems to be the answer.

David, I also experience the logs being shipped but error at the restore stage.

And to be clear with Gangandeep: you cannot have a maintenance plan of full backups with a maintenance plan of log shipping ?

Ian Yates SSCoach Points: 19738 More actions · Answer 4

David, you have the same problem, although manually created by yourself. The FULL database backups WILL interfere with your customised log shipping for the same reason that the SQL native log shipping will fail. In your case David, after taking a full backup, you must then ship this full backup over to the other server and restore it - then you can ship & restore your transaction logs each night. BUT, if you take another full backup, you must ship this over and restore it, and then apply its transaction logs.

The same situation arises when using any file system backup tool that uses archive bits, etc. The transactional backups that you take build upon firstly the most recent FULL backup and then the most recent differential backup (neither of you are using these) and then each transaction log backup in sequence after that. You cannot take a full backup, then have some log backups, and then take both a full backup and a log backup and expect the logs to continue building upon eachother without capturing the full backup in the middle as well... Unfortunately it doesn't work like that.

If it did work, then any work done in the DB between the last transaction log backup and the following full backup would be lost as the full backup would record these and the log backup would not see them.

As a workaround, if you really want to have a local full backup of your DB but still do your log shipping, you should just do your initial full backup, then your log backups forever after. For a local full backup, detach your database, back up its files using any file backup utility, then attach the database. Your DB will be offline for the duration of the full backup.

Let us know how you go!

David le Quesne SSCertifiable Points: 5229 More actions · Answer 5

Hi,

I have solved one part of my problem There IS a timeout setting when executing remote stored procedures. On the Properties dialog of the source database server (the one from which the backups are being generated) look for the Connections tab and check the Query Time Out (sec. 0=unlimited) setting.

This setting is the timeout for queries executed on a remote connection (eg: linked servers). The default seems to be 600s (ten minutes), but on my production server, for reasons lost to history, it was set to 60s!

If you are trying to restore a 200K transaction log, this is not a problem. But for a 200Mb log or 600Mb database, the query will time out if it takes more than 10 minutes (or 1 minute on my server). However, it does not seem to generate an error! The job history reports success, but the remote procedure step history does not include the text from the restore operation, and subsequent transaction logs fail because the restore never completed

Changing this query timeout setting to 900s gave the query enough time to execute the remote stored procedure and perform a 600Mb database restore. I will wait and see if the transaction logs continue to restore correctly.

Cheers for your help!

David

If it ain't broke, don't fix it...

Ian Yates SSCoach Points: 19738 More actions · Answer 6

Thanks for posting the solution - nice to know how these problems are solved. Didn't occur to me that you were using remote proc execution - even then I wouldn't have thought about the timeout. Something to keep in mind for the future

Hope the rest of it keeps functioning for you too!

DB King SSC Eights! Points: 806 More actions · Answer 7

So how do you setup SQL Server to ship full backups ? Is there a wizard to do that ?

Ian Yates SSCoach Points: 19738 More actions · Answer 8

I've never played with the proper "log shipping" feature of SQL Enterprise so I am not sure. But the idea of "log shipping" is to have the initial full backup shipped and then you only ship the incremental changes to the database (the log backups). I imagine there might be a way to have it refresh periodically with a full backup - this would be akin to using replication and sending out a new snapshot... If I have some time later I might investigate it...