Re: Bring Online down in middle of transaction rollback?

>What is the effect of bringing Online down in the middle of a >transaction being rolled back? Is it meant to recover gracefully from >this kind of thing? If so, in reality, does it actually do so (recover >gracefully, that is)? > >I've got a client who pulled a bone-headed move of running two huge >deletes at the same time. After eight hours, the 800,000 row locks have >been exhausted, and all users are locked out of the database. So, we're >going to have to wait another eight hours, at least, for the >transactions to roll back. > >I'm wondering if there are any ways to speed this process. One thought >that came to mind was to bring Online down, add more locks, then bring >it back up. My concern is whether Online is meant to recover gracefully >from being shut down in a middle of a transaction rolling back. 800,000 >rows is a lot to modify, so I'm a bit queasy about interrupting that >kind of transaction. Anybody w/ experience like this? > >Is there some other way that I could speed this process to get users >access to the DB more quickly? When online is brought up the recovery process is a two phase recovery. The first phase (physical recovery) uses the physical log file and is used to bring the database back to the state that it was at the last checkpoint. Then begins logical recovery which uses the logical log files. This involves rolling forward all activity contained in the logical log files. Finally when the end of the logical log file is encountered, all open transactions are automatically rolled back. Since the recovery begins at the last checkpoint, then re-rolling back a transaction that was in rollback when the engine was brought down poses no problem. Basically, whatever was still in an open state will be rolled back. If it was partially rolled back, it doesn't matter. The recovery process will complete the roll back. Madison Pruet