The Database Could Not Be Exclusively Locked to Perform the Operation (Error 5030)

Something is connected to the database, and the operation you asked for needs everyone out. Renames, collation changes, SET SINGLE_USER, detach and several ALTER DATABASE options all need an exclusive lock. The connection in the way is very often your own.

See Who Is Actually In There

SELECT  s.session_id,
        s.login_name,
        s.host_name,
        s.program_name,
        s.status,
        s.last_request_end_time
FROM    sys.dm_exec_sessions AS s
WHERE   s.database_id = DB_ID(N'YourDb')
ORDER   BY s.last_request_end_time;

Read the program_name column before you kill anything. Three answers come up constantly:

The Two Traps

Your own window counts. If your query window’s context is the database you are altering, you are the blocker. Switch to master first, then run the statement:

USE master;
GO
ALTER DATABASE YourDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Object Explorer counts too, and it reconnects on its own. Expanding the database node in SSMS is enough to take a connection, which is why the operation fails again thirty seconds after you cleared everything.

WITH ROLLBACK IMMEDIATE is what makes this work: it rolls back every open transaction in that database rather than waiting. That is a data-losing action for anyone mid-transaction, so it belongs in a maintenance window, not in a working day.

Then Do the Work, Then Put It Back

USE master;
GO
ALTER DATABASE YourDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE YourDb MODIFY NAME = YourDbArchive;
GO
ALTER DATABASE YourDbArchive SET MULTI_USER;

Never leave a database in SINGLE_USER at the end of a session. An application connection pool will take the one available slot within seconds and you will be locked out of your own database.

Locked Out After Setting Single User

It happens, and the recovery is not obvious, because you cannot get in to fix it.

SELECT s.session_id, s.login_name, s.program_name
FROM   sys.dm_exec_sessions AS s
WHERE  s.database_id = DB_ID(N'YourDb');

KILL 57;   -- the session holding the single slot

Run that from master, kill the holder, and immediately run SET MULTI_USER before anything else reconnects. If you are racing an application pool, stop the application or take the database offline rather than losing the race repeatedly.

What Not To Do

Do not put SET SINGLE_USER WITH ROLLBACK IMMEDIATE in a scheduled job as a way of making a maintenance script reliable. It converts other people’s committed work into rolled-back work on a timer, and the job’s own history will not record what it destroyed.

Related Scripts

• Get Active Connections by Database, see who is connected before you disconnect anyone
• Kill All User Sessions on a Database, clearing the database when you need exclusive access
• The Database Is in Single User Mode, the state this can leave you in, and the way back out

Common Questions

Related Scripts

• Get Active Connections by Database, see who is connected before you disconnect anyone
• Kill All User Sessions on a Database, clearing the database when you need exclusive access
• The Database Is in Single User Mode, the state this can leave you in, and the way back out