SQL Server Performance Forum – Threads Archive

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: erogram FilesMicrosoft SQL ServerMSSQLBACKUP
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: erogram FilesMicrosoft SQL ServerMSSQLBACKUP
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: Drogram FilesMicrosoft SQL ServerMSSQLLOG
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

---

I’m sure this was referred many times in the forum, so please take time to search under Log SHipping section. Also check event viewer logs and SQL Agent account privilege on both the servers to avoid any network related issues. In general for error 4305 it is suggested to The error 4305 indicates that the restore operation found a gap between the last restore and the transaction log that you attempted to apply. So, to resolve this error you should restore transaction logs in the same order in which they were backed up
You must also check any non-logged transactions happended after the last backup and before first transaction log backup job. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I have recieved a solution from another site which indicates you cannot have two maintenance plans:
1) full backups
2) log shipping Is this correct ?

---

No you can have, but you cannot execute them at sametime and avoid any overlapping of full backup and log backup. You can have different plans for full backup and log backup without any problem, avoid log backup job during full backup job schedule. BTW what was that site referring this point? Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

it was on sqlservercentral http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=241868) and Gagandeep singh wrote 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. i’ve asked him to clarify, but he hasn’t replied. Actually, I had the logshipping stop at 00:00. The schedule for the log shipping was 02:30 – 23:59 but it didnt work even though the full backups are done at 02:00. Any suggestions ? Also does it make a difference if the clocks of the two machines are out by a frew minutes ?

---

As Gagandeep singh writes this is your problem. You can not have two backup plans on the same database since it will screw up the LSN numbers when log shipping.

---

To provide further information on this, you can have a maintenance plan in addition to the shipping plan as long as it does not backup the transaction log. You can have another plan to do a full database backup, optimizations, etc without impacting your shipping (naturally, you don’t want them running at the same time). Once you setup shipping, all that matters is that your transaction logs are always backed up within the shipping jobs. This way, your MSDB.dbo.Shipping* tables get updated with the new log information, the logs are copied to the destination and restored. If you manually dump the TLogs or setup another plan to do Tlog backups, you get out of sync.

---

]]>