Solved

Log Shipping Stops after 3/4 days

Posted on 2011-03-08
10
316 Views
Last Modified: 2012-05-11
Ive setup log shipping for two of my databases to a server so I can run reports against the 2nd server. Once I set it up, the process runs quite happily for 3/4 days then the restore process of the logs to the secondary server starts to fail. The copying of the files is still working its just the restore side which is failing. The only way to get this back up and running is to remove the log shipping options and resetup the process.

How can I identify the problem? Ive checked the job history of the restore process and this is the error.


Date            08/03/2011 10:10:00
Log            Job History (LSRestore_HOGWARTS_GoldMine)

Step ID            1
Server            CLONER
Job Name            LSRestore_HOGWARTS_GoldMine
Step Name            Log shipping restore log job step.
Duration            00:00:00
Sql Severity            0
Sql Message ID            0
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0

Message
Executed as user: CLONER\SYSTEM. The step failed.
0
Comment
Question by:daiwhyte
  • 5
  • 5
10 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35068135
I suppose you are shipping your logs with standby option.
When your log shipped database is used in some queries no logs can be restored. Maybe this is your issue?
I'd suggest you to think about mirroring as an option. You can mirror your database and in mirror server run snapshots against mirrored db. Then you can generate raports using snapshot.

Take care,
Daniel
0
 

Author Comment

by:daiwhyte
ID: 35068369
Ive gone through more of the job history and found this which could have something to do with the issue.

Message
2011-03-08 11:44:28.12      *** Error: The file '\\cloner\db2\UpdateGM_20110305113001.trn' is too recent to apply to the secondary database 'UpdateGM'.(Microsoft.SqlServer.Management.LogShipping) ***
2011-03-08 11:44:28.12      *** Error: The log in this backup set begins at LSN 7634000000012500001, which is too recent to apply to the database. An earlier log backup that includes LSN 7632000000345400001 can be restored.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Looks like the process is out of sync?
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35068597
I think that your logs were not restored because of some queries or activity in second database.
As I wrote before, activity in db that participates in log shiping makes it irrecoverable. What happened was that logs retention policy deleted old logs from your share and new ones did not fit.
Can you keep logs longer in your share so that log shipping process can restore them after db activity is finished?
0
 

Author Comment

by:daiwhyte
ID: 35068709
Sure, I can leave the logs as long as you like whats the best setting for this?

Once the set time of retention has elasped, will the process fail at that point?

What I want is to set it to a value which will cause the process to fall over.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35068731
I think you need to determine the best value by yourself.
What I can suggest is that you need to observe when you database is in use - how long does it take?
Next you need to determine how long does it take for your server to restore unrestored logs.
You need to keep logs as long as they are not restored.
Maybe scheduling reports execution to low transaction time in your enviroment will help - if you can schedule them like that ;)
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35068740
Let me correct myself:
You need to keep logs at least as long as they are not restored ;)
0
 

Author Comment

by:daiwhyte
ID: 35068745
Ok, that makes sense. I run a report on Friday at 1 and its at this point the log shipping falis. Im going to change it so the log shipping doesnt take place during that time.
0
 

Author Comment

by:daiwhyte
ID: 35068748
Right, yes - that makes sense in terms of keeping the logs.

Im going to set everything back up now and test.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35068831
I hope this helps, in case of any questions don't hesitate to post :)
In case I was helpful please mark right answer as helful. I'm appreciated.

Take care,
Daniel
0
 

Author Closing Comment

by:daiwhyte
ID: 35069440
Thank you.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now