Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Solved
Log Shipping Stops after 3/4 days
Posted on 2011-03-08
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_GoldMi
Step ID 1
Server CLONER
Job Name LSRestore_HOGWARTS_GoldMin
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.
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_GoldMi
Step ID 1
Server CLONER
Job Name LSRestore_HOGWARTS_GoldMin
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
5
10 Comments
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
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
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_201
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?
Message
2011-03-08 11:44:28.12 *** Error: The file '\\cloner\db2\UpdateGM_201
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?
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?
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?
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.
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.
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 ;)
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 ;)
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.
Right, yes - that makes sense in terms of keeping the logs.
Im going to set everything back up now and test.
Im going to set everything back up now and test.
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
by Mark Wills
Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server... So if you are the accidental DBA, or, simp…
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…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month5 days, 16 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.