November 27, 2008 at 6:16 am
Im new to the DBA world.
I am doing logshipping from 4 prods to 1 secondary, recently we start a new division and haing endless problems with the restore.
current settings
Copy Log on Primary 15mins
Copy log on Secondary 15mins
restore log on secondary once a day (nightly)
error in logship monitor
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4326: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set terminates at LSN XXX, which is too early to apply to the database. A more recent log backup that includes LSN XXX can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
I took a back up from prod and restored on secondary but i still see the same error.I have remove logshipping and re-created it again, this proves to run for at less a half day. after test the logship setting it 4 hour restore time.
the other enviroments are working fime.
any advise would be greatly appreciated
November 27, 2008 at 6:40 am
Nazier.vdrheede (11/27/2008)
The log in this backup set terminates at LSN XXX, which is too early to apply to the database. A more recent log backup that includes LSN XXX can be restored.
What that's saying is that the log backup doesn't match the full backup. Once you've restored the full backup onto the secondary server you need to ship only the log backups that occurred after the full backup was taken.
Also check to make sure that the log on the primary server is not ever getting truncated or the recovery model getting set to simple and back to full.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2008 at 2:24 am
Hi Gail,
thank you for response and Im sorry for only comming back to you now. just want to clarify the last part, if I created the db in simple then change it to full, will this cause problems with logshipping, also I have created an archieve folder within the same share folder for logshipping, will this cause any problems?
i do thank you for your time and value your comments.
November 28, 2008 at 2:29 am
Also when i make a full back up on my prod do I have to un check the remove inactive entries from transaction log under options?
November 28, 2008 at 2:56 am
I have change the db option to bulk- logged , made a full back up remove inactive entries, clear all trns, restore the db to the secondary Force restore over existing db, change paths as per normal and checked leave database read-only and able to restore additional transaction logs.
After the restore it fails complaining giving the same error, under the db properties i looked at the last last back up date which is current and the last trn yesterday I them copied the trn back to the share and the job ran successful but the logship monitor still gives the same error
any adivise as to what i am doing wrong
November 28, 2008 at 4:01 am
You should only restore the log backups that were taken after the full backup. Not before.
Why bulk-logged? Are you doing lots of bulk operations?
If you create a DB in simple and then switch it to full, you'll have to take a full backup before you can start taking tran log backups. Other than that, no issues.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 28, 2008 at 4:11 am
Hi Nazier,
You need to establish whether it is the log shipping that is failing (i.e. the backup/copy/restore jobs) or the Monitoring jobs (what are your Alert Thresholds?):
"..and the job ran successful but the logship monitor still gives the same error".
It might be that everything is working but for some reason your Primary server and/or Secondary server are unable to connect to the Monitor to update the following tables (in msdb of the Monitor):
log_shipping_monitor_primary
log_shipping_monitor_secondary
These tables are also present on the Primary and Secondary. On the Primary, look at log_shipping_monitor_primary to get the last backed up file and on the Secondary in log_shipping_monitor_secondary to get last copied file. These should match to what you find in the corresponding tables on the Monitor. (in 2005 you can also use the
sp_help_log_shipping_monitor_secondary and sp_help_log_shipping_monitor_primary stored procedures but as far as I remember they are not present in 2000).
If this is not the issue; can you clarify; after you have set up logshipping it initially works ("and able to restore additional transaction logs") and then fails "After restore it fails" - which restore?
Can you outline the flow of events and specify which action is taken on the Primary and which is taken on the Secondary.
Basically the flow should, simplified, be like this:
1. Database in Primary set to FULL recovery mode
2. Take a Full backup of the Primary DB
3. Restore the full backup from Step 2 on the Secondary specifying either WITH NORECOVERY or STANDBY
4. Set up logshipping specifying that the secondary is already initialized (terminology is different in 2000 and 2005)
Now you should be good to go. If you continue to have problems, pleasy specify which version of SQL Server you are using and maybe you also want to consult the "SQL Server 2000 How to Set up Log Shipping" whitepaper (http://www.microsoft.com/DownLoads/details.aspx?FamilyID=7395ec1b-199f-42bc-a31b-2056adf73f94&displaylang=en)
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply