Termination mode

Note: Along with user mode the following termination option can be specified ROLLBACK AFTER integer, ROLLBACK IMMEDIATE, NO_WAIT when changing database mode.

NO_WAIT – This option will check for connections if open before changing the state, if there are open connections it fail.

Example: Switching from MULTI_USER to SINGLE_USER
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH NO_WAIT

If multiple connections are open SQL Server will return the following message:
Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database ‘AdventureWorks’
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

ROLLBACK IMMEDIATE – will terminate connections immediately. All incomplete transaction will be rolled back so depending on the transaction it may take some time to terminate.

Example:
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ROLLBACK AFTER integer – Will terminate connections after the number of seconds has passed, incomplete transactions will be terminated.
ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK AFTER integer

2 thoughts on “Termination mode

  1. Pingback: Database user mode « Oracle Spin

  2. Pingback: How to set database read only/read write? « Oracle Spin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s