57

Having executed a DB deploy (from a VS SQL Server database project) on a local database, which failed, the database has been left in a state where it has single user mode left on (the deploy runs as single user mode).

When I connect to it from SSMS and try something like the following:

ALTER DATABASE MyDatabase
SET MULTI_USER;
GO

I get the error:

Changes to the state or options of database 'MyDatabase' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

I tried taking the database offline, which SSMS tells me succeeds, but it doesn't appear to actually do anything. So far, I've only been able to get around this by dropping and recreating the database (which is kind of okay, because it's only a local test database). However, I'd like to be able to reset the status.

How can I convince SQL Server to take this database out of single user mode?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Paul Michaels
  • 16,185
  • 43
  • 146
  • 269

5 Answers5

185

In first run following query in master database

exec sp_who

If you can't find the culprit, try

SELECT request_session_id FROM sys.dm_tran_locks 
WHERE resource_database_id = DB_ID('YourDatabase')

Then kill all process that use your database with following query:

KILL spid

Then run following query:

USE Master
ALTER DATABASE YourDatabase SET MULTI_USER
GôTô
  • 7,974
  • 3
  • 32
  • 43
mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
  • 8
    If you cannot find any suspicious looking entries in in the sp_who output, this query has helped me out - SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('YourDatabase ') – earthling42 Jan 13 '16 at 15:46
  • 1
    @earthling42: I added your (very useful) query to the answer – GôTô Apr 15 '16 at 14:53
  • I was stuck in a similar state. But I had several spid which were connected or in waiting state. I realized that the server(application) using the database was creating connections as soon as I killed an existing connection and my console would never get a connection in this manner. So, I had to shut down all such servers first such that they do not contend for a connection. Only then could I run the query mentioned successfully and recover. – Rajeev Ranjan Jun 17 '17 at 06:40
4

Try the below commands

First run these three commands

USE [master] 
SET DEADLOCK_PRIORITY HIGH
exec sp_dboption MyDBName, 'single user', 'FALSE';

Second run these two commands

ALTER DATABASE MyDBName SET MULTI_USER WITH NO_WAIT
ALTER DATABASE MyDBName SET MULTI_USER WITH ROLLBACK IMMEDIATE
Pankil Agrawal
  • 75
  • 11
3

This was answered here, the code is:

use master
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

--do you stuff here 

ALTER DATABASE YourDatabase SET MULTI_USER
Community
  • 1
  • 1
Tristan
  • 1,004
  • 7
  • 14
  • 1
    I don't believe that's the same issue - the DB will allow neither of those staements – Paul Michaels Jul 07 '14 at 10:52
  • did you replace master with your database name? also, do you have admin privileges on the database you want to alter? – Tristan Jul 07 '14 at 10:55
  • 1
    Just add, this is not the correct answer. Once the db is in single user mode *and* is occupied by a session, the alter database command will NOT succeed. You need to kill the session that is occupying the db first, and then you can alter db to multi_user mode. – jyao Dec 17 '19 at 05:33
  • This was the only one that worked for me. – C. Graham Jun 16 '23 at 14:20
0

Use DAC (Dedicated Admin Connection). Make sure you have enabled it first In SSMS type in admin: for Server Name after connecting to master ALTER DATABASE SET MULTI_USER

Ankit
  • 142
  • 3
-1

To force the update use " with rollback immediate"

ALTER DATABASE [DATABASE_NAME] SET MULTI_USER  with rollback immediate
NajiMakhoul
  • 1,623
  • 2
  • 16
  • 30
Youssef Ajrindou
  • 1