Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Transaction Termination for Changing Database States

SQL Server 2000

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.

See Also

ALTER DATABASE

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.