new recovery model in SQL Server 2005

It seems that SQL Server 2005 is changing the recovery model. I have experience with Oracle and *I think* this is the same model. One thing I noticed in Oracle was sometimes this fast recovery model helped me, and sometimes it didn't. It helped when perhaps a large table was undergoing recovery and I could give users access to the other tables. It hurt me when recovery wasn't localised like that, and all the users ended up submitting the same TX that they issued when the outage occurred. So what happened, the recovery process thrashed as it tried to recover the pages that the users requested which needed recovery in the first place.

Does SQL Server 2005 new model work the same way. Is there a switch for me to use to let me choose the recovery model I want based on the application perhaps?Just to clarify, like SQL2000, the SQL2005 supports the same three recovery models: FULL, BULK_LOGGED and SIMPLE. May be the point you are referring to is that the database is made available immediately after REDO phase is completed in SQL2005. However, the transactions that are being rolled back during UNDO phase acquire the same locks as they had before the crash and will block/allow concurrent access based on lock compatibility as described in BOL. I am not sure what you meant by 'recovery process thrashed'.

Thanks|||Sunil - what I meant that if it is like Oracle, then the recovery process will go through and recovery pages in a certain order. But if a user wants a page, the engine will go and retrieve that one, even if it is out of order. Is that what SQL Server does? That way users can access pages that don't need recovery as it works through the ones that do, and if a user requests a page that needs recovery, it will go and get it. If 100 users all ask for pages and the engine goes to recover them, it kind of thrashes the recovery process. In some apps in my experience this feature is great, others, no so great. I wanted to know if I could turn this off or on per the database level too (doubt it)(. Thanks!|||That is not quite what SQL does.
SQL 2005 completes the redo phase prior to bringing the database online.
At this point, the database is in the state it was in when the backup was taken, including any transactions which were in progress.

Those transactions may hold locks, as Sunil noted.
At this point, users may access any page in the database which does not conflict with a lock.

In the background, the engine performs the Undo phase to roll back any transactions which were incomplete when the backup was taken, releasing locks as it goes.

None of the background operations are done out of order as a result of client query activity, so we do not thrash the recovery process.

Is this more clear?

Kevin