Microsoft® SQL Server™ 2000 includes the ability to easily shut down or otherwise change the state of a database, automatically terminating the sessions of affected users and rolling back the associated transactions. Affected sessions may be terminated immediately, or may be allowed to continue to their normal conclusion with an optional time-out.
It is often necessary to stop or restrict activity on a database to perform maintenance or other operations without taking down the server, for example:
Transitioning into any of these states requires the termination of transactions and the associated sessions that do not meet the requirements of the new state.
There are three types of transaction termination:
New transactions are prevented from starting. Incomplete transactions are allowed to commit or rollback on their own.
New transactions are prevented from starting. Incomplete transactions are allowed to commit or roll back on their own until the time-out is reached, at which time they are rolled back.
An immediate termination prevents new transactions from starting, and rolls back incomplete transactions unconditionally.
The user initiating the change remains connected and able to perform further commands.
Use the ALTER DATABASE statement to specify the database state and a transaction termination type.