I ran into an issue the other day and wanted to ensure that I shared it out there. In a Log Shipping scenario, you can occasionally run into a corrupt transaction log backup. This causes the database to stay in restoring mode (as mine was previously in a standby/read-only state for reporting), but requires the next LSN to be restored, which you don’t have.
In this case, it went overnight and was out of sync a ways, but at night I do a differential backup of the database. By restoring the differential backup with norecovery to the log shipped database, it allowed me to continue restoring with log backups from that time on.
Hope that helps anyone in the www realm, it is a nice save since databases can be large and the initialization could be very impactful.
Thanks for the heads up.. I often have to do this when I am reporting as well.. It’s nice to know there are other alternatives. Thanks for the info!
Hi, Brian:
I saw one of your comments on Expert-Exchange re: log shipping and decided to conact you. I will appreciate your comment on my idea, please:
SQL Server Log Shipping “Graceful” Restore
I HAVE NOT TRIED THIS YET, BUT IT WILL BE A MORE ELEGANT AND “VERSITILE” SOLUTION. PLEASE READ CAREFULLY AND GIVE MY YOUR FEEDBACK:
I am getting ready to replace SQL 2005 Replication with Log Shipping …
(this has already been decided and is not why I am asking this?)
Here is the problem that I would like your expert opinions on:
After setting up LS, when the logs are restored, I don’t want the option
to kill all connections and restore.
I also don’t want to wait until all connections are completed … as this may be a very long wait.
What do you think of this idea:
1- Run a job before the scheduled log restore jobe is to be run
2- This job will check for the STATUS of each SPID (RUNNING, SUSPENDED, RUNNABLE, PENDING, BACKGROUND, SLEEPING) and kill everything thta is not in “RUNNING” state.
3- If there are no running processes, then restore log.
4- If there are RUNNING processes, it will repeate steps 2 and 3 …
I need to come up with a solution that will ensure that there is minimul interruption to people using the DB fro reports.
I will appreciate your comment.
Thanks,
D
I think that you can do something like what you are proposing, but let me add some feedback to your idea.
In 2, you will check the status of the session, but SUSPENDED is a valid state, which means that it “was” running and is now waiting. If you kill that SPID, you may be rolling back some transaction that may be large as well. Other status’ may be OK.