Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am starting to research setting up log shipping with our server. I am thinking I want to use standby mode for the remote server for the things I want to do (Data checks, ect.) What I wanted to know and have not been able to really find a definite answer on is once I create a undo file with the standby mode of restoring the database,

  1. How large is that file normally?
  2. Does it grow over time?
  3. Is it worth it (or even possible) to drop in to NO_RECOVERY mode when I don't need to access the database and switch back in to stand by mode when I am doing a data check?
share|improve this question
up vote 1 down vote accepted
  1. Normally, the file is quite small - definitely smaller than the log backup. Restoring the log WITH STANDBY runs the REDO part of recovery, then runs the UNDO part of recovery, but saves the details of what it had to do for UNDO into a file - the undo file. If there's a lot of transaction log that has to be undone (i.e. rolling back transactions that weren't committed at the time the log backup completed), then the file could be larger than usual.

  2. The file does not grow over time. It will be deleted and recreated with each subsequent restore of a transaction log WITH STANDBY.

  3. It is possible to go between STANDBY and NO_RECOVERY using the undo file. If the database is normally being accessed between log shipping restores, it makes sense to put the database into STANDBY mode. I have some in my environment that are used for number crunching at night. If the point of the log shipping is simply to have a warm standby and no access is needed, just restore with NO RECOVERY.

share|improve this answer
Thanks for 1 and 2, for number 3 I will be doing exactly what you are doing. No use during the day but once a week some number crunching will be done on it. Would you recommend leaving it in NO RECOVERY until the number crunching or leave it in STANDBY? – Scott Chamberlain Oct 20 '10 at 18:20
It's a little tricky to get it to go into STANDBY only at certain times. You could stop the log shipping restores for a period, run a script to restore WITH STANDBY, crunch numbers, and then rollback the UNDO file. If that's too much trouble, I'd just recommend restoring with STANDBY as part of the log shipping restore. – Brian Knight Oct 20 '10 at 18:40

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.