There's More Than One Way to Kill a Database Connection

It’s not uncommon for a DBA to have to remove all user connections from a database when performing certain maintenance or upgrade tasks. This could be due to exclusivity rules for the upgrade. It may also be to protect data integrity and customer experience during a migration where you want to ensure a clean slate for the change.

Option 1: The Simple but Insufficient Approach: Offline the Database

Using this method we simply set the database offline and bringing it back online. While simple, this process will not complete until any existing transactions complete and sessions close on their own. This is not an effective option for gaining exclusive control of the database and I advise against its use.

ALTER DATABASE [database name] SET OFFLINE;

You can force all open transactions to rollback and sessions to close with the additional ROLLBACK IMMEDIATE clause as such but keep in mind that as a DBA we try to avoid issuing commands that negatively affect end users’ experiences in the databases we support (plus we alter the expectations of our end users doing such:

ALTER DATABASE [database name] SET OFFLINE WITH ROLLBACK IMMEDIATE;

Pros:

Cons:

I’d avoid using this method unless you have a solid understanding of how the application and end users utilize the database and are comfortable that the above caveats are compatible with your mission to gain exclusive access.

Option 2: Dynamic SQL to Kill All User Sessions for the Database

It’s possible to use the sys.dm_exec_sessions Dynamic Management View to identify all user sessions for a specific database – or all databases if the change being implemented is server-scoped – and create a dynamic KILL statement for each applicable session that is returned from the following query:

/* For a specific database */

SELECT 'KILL ' + CAST(session_id AS VARCHAR(10))
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
       AND database_id = DB_ID('database name');

/* For all databases */

SELECT 'KILL ' + CAST(session_id AS VARCHAR(10))
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

Pros:

Cons:

Option 3: Alter the Database to SINGLE_USER or RESTRICTED_USER

There are three different options for databases as it pertains to allowing users to connect: MULTI_USER, SINGLE_USER, and RESTRICTED_USER. Typically a database will be under a MULTI_USER setting which means what it says: multiple users can connect at any given time. Under SINGLE_USER the database can maintain a single session against it and when that session is open no other sessions can be spawned against the database. Then there is RESTRICTED_USER which allows for any user that is a member of the db_owner database role or member of the sysadmin or dbcreator server role to connect to the database but all other users are blocked from connecting. Changing this setting, like the first option, is subject to letting all open sessions that are not members of those privileged roles to finish their work before the ALTER DATABASE statement completes.

The code for either option is:

ALTER DATABASE [database name] SET SINGLE_USER | RESTRICTED_USER;

If it’s acceptable to roll back all open transactions against the database you can augment the above command as follows but remember all the issues I’ve already outlined around use of WITH ROLLBACK IMMEDIATE:

ALTER DATABASE [database name] SET SINGLE_USER | RESTRICTED_USER
WITH ROLLBACK IMMEDIATE;

When you’re done the command to revert back to MULTI_USER is:

ALTER DATABASE [database name] SET MULTI_USER;

Pros:

Cons:

Additional Considerations

Depending on the nature of the usage of the database(s) in question I highly recommend that application connectivity first be limited wherever possible in conjunction to these changes. It’s a possibility for user connections to be re-established if you don’t limit users from attempting to get back into the database(s) after following any of these options above.

Once again it goes to show that when you’re dealing with technology it’s never an “I” situation, but rather a “We” situation where multiple teams are responsible for the success of the entire project.