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;
- Transactions are allowed to complete before connections are severed if you don’t issue the
ROLLBACK IMMEDIATEcommand, simple process to execute.
- Depending on the transactions are open you can find yourself waiting for the offline command to complete when you don’t include
- Open sessions that have no active transaction are not killed and closed unless
ROLLBACK IMMEDIATEis employed so technically this option doesn’t serve the goals of this article.
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))
WHERE is_user_process = 1
AND database_id = DB_ID('database name');
/* For all databases */
SELECT 'KILL ' + CAST(session_id AS VARCHAR(10))
WHERE is_user_process = 1;
- While the code is a bit more advanced than the first option, you have the code now (thanks to this article).
- There is a timing issue between executing the query to get the dynamic SQL and running that output dynamic SQL during which time new sessions are created that would not be in the scope of the dynamic SQL or worse, those sessions could complete and the session_id values you’re killing may be associated with sessions that have nothing to do with the database you’re working against.
- Sessions are rolled back during a KILL and users may expect that their transactions were committed but were instead rolled back.
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:
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
ALTER DATABASE [database name] SET MULTI_USER;
- Transactions are allowed to complete before connections are severed (if not including the
WITH ROLLBACK IMMEDIATEclause)
- Still allows privileged users to connect if using the
RESTRICTED_USERroute. If you’ve properly issued rights to best practices you should not have any end users with that level of permissions that would allow them continued access. The only users that should have that level of access are DBAs or IT staff specifically responsible for administration of the data environment and whom are likely the individuals running the process to perform the upgrade/migration process that has you consulting this article.
- Depending on the transactions are open you can find yourself waiting for the offline command to complete if you don’t use the
WITH ROLLBACK IMMEDIATEclause.
- If using
SINGLE_USER, I recommend that be incorporated into the upgrade script at the head of the script. Otherwise, once you close the session that executed the
ALTER DATABASE… SET SINGLE_USERstatement an end user can seize control of the database and you’d be prevented from connecting or altering the database until that session is closed and no others attempt to connect.
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.