Transaction Termination for Changing Database States
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:
- Single-user mode: only one user is allowed
- Restricted-user mode: only members of the db_owner, dbcreator, or sysadmin roles are allowed
- Offline: the database is offline
- Read-only mode: no changes are allowed
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:
- Normal
New transactions are prevented from starting. Incomplete transactions are allowed to commit or rollback on their own.
- Normal with time-out
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.
- Immediate
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.