Set database from SINGLE USER mode to MULTI USER

sql

database-management

sql-server

multi-user-mode

byAnton Shumikhin·Sep 28, 2024

To switchover your SQL Server database to the MULTI_USER mode, use the below command:

-- "Avada Kedavra" for SINGLE USER mode 😎
ALTER DATABASE [YourDB] SET MULTI_USER;

Remember to replace [YourDB] with your actual database name to allow multiple connections.

Before you run the command, confirm that no active connections exist, like so:

-- "Are we alone?" code equivalent 👽
SELECT * FROM sys.dm_exec_sessions WHERE database_id = DB_ID('YourDB');

If there are connected sessions, you have the authority to disconnect them:

-- "It's not you, it's me" in SQL-ese 💔
KILL [session_id];

Remember, always ensure that you're not abruptly interrupting anything before switching to MULTI_USER mode.

Checking active connections and saying goodbye

When dealing with database modes, it's crucial to properly handle active connections. They can prevent database mode changes if not addressed. Here's how you can check and politely ask them to leave:

Active Sessions Detector 3000

Run sp_who command to list out active sessions:

-- "Who's there?" but it's SQL talking 🦉
EXEC sp_who;

Active Sessions Terminator 2022

To disconnect an active connection, use its SPID:

-- "I'll be back" but it's you saying it to the session 😎
KILL [SPID];

Remember, this command will perform a roll back on any uncommitted transactions for that session.

Plan B for when your commands don't get the memo

Occasionally, the transition might run into some snags. When that happens, here's your troubleshooting kit:

Pick master database

Swap over to the master database to avoid any entanglements with sessions:

-- Changing lanes: Now in the master's territory 😇
USE master;

Forceful transition

Are some sessions reluctant to take a hint? Use the WITH ROLLBACK IMMEDIATE option to force closure:

-- "Move over, I got this!" statement for SQL Server 💪
ALTER DATABASE [YourDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

SSMS being a bit whimsical?

If you're using SQL Server Management Studio and it decides to play hard to get, give your SQL Server service a nice little restart. If that doesn't nudge it, flex your command-line prowess.

Fool-proof practices and bonus tips

Switching between SINGLE_USER and MULTI_USER modes too often can introduce issues. If you have to switch, make sure you automate checks and disconnects within your scripts to prevent manual errors.