How large can the undo file grow to?

Question

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?

Answer 1

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.