Exit single-user mode

Question

Currently, my database is in Single User mode. When I try to expand me database, I get an error:

The database 'my_db' is not accessible.(ObjectExplorer)

Also, when I try to delete the database, I get the error:

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

How do I exit out of single-user mode? I don't have any user using this database.

When I try to browse my site with IIS, the error I get is:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

I feel as though the single-user mode is causing this.

You might have detached the database. Please disconnect the connection and connect the server again., Then right click on databases folder, click attach & choose you database mdf file from your hard-disk. You can see that single-user mode will not be there anymore. — Praveen Patel G, Oct 04 '21 at 09:26

score 482 · Accepted Answer · edited Oct 13 '22 at 17:51

482

SSMS in general uses several connections to the database behind the scenes. You will need to kill these connections before changing the access mode:

First, make sure the object explorer is pointed to a system database like master.
Second, execute a sp_who2 and find all the connections to database 'my_db'. Kill all the connections by doing KILL { session id } where session id is the SPID listed by sp_who2.
Third, open a new query window.

After those 3 steps, execute the following code.

-- Start in master
USE MASTER;

-- Add users
ALTER DATABASE [my_db] SET MULTI_USER
GO

See my blog article on managing database files. This was written for moving files, but user management is the same.

edited Oct 13 '22 at 17:51

luiscla27

4,956
37
49

answered Sep 23 '13 at 18:19

CRAFTY DBA

14,351
4
26
30

5

When I used the 'sp_who2' command, I did not see any DBName connected to 'my_db' I did not kill any of those connections. After I ran the commands I get the same error =[ : 'Changes to the state or options of database 'my_db' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed' – Liondancer Sep 23 '13 at 18:38
2

Did you make sure you are in master, sp_who2 does not show any rows with database = my_db, and your object explorer is not on the my_db. – CRAFTY DBA Sep 23 '13 at 18:48
1

Try, disconnecting and connecting SSMS. Something has to be connected to that database. The other option is to connect with the dedicated admin console (DAC). This assumes you are a sysadmin. Then kill the offending spid. – CRAFTY DBA Sep 23 '13 at 18:49
2

Also, down load my usp_who2 script (http://craftydba.com/wp-content/uploads/2011/09/usp-who2.txt). Execute it. It places the utility in msdb.dbo.usp_who2. It saves the results of sp_who2 into a table in tempdb under your user id, filter by the database name. Post an image of the error to help us more. Good luck. – CRAFTY DBA Sep 23 '13 at 18:53
I actually found a connection to 'my_db' under dbname. However I'm not sure how to kill this connection – Liondancer Sep 23 '13 at 19:32
3

Find the SPID, use the following: Kill 100. The 100 is the number of the session (SPID). – CRAFTY DBA Sep 23 '13 at 19:59
It doesn't show SPID for my case too. The only way to stop backuping was to delete DB. – cassandrad Sep 21 '15 at 14:47
Killing some system actions may take a long time if a ROLLBACK is required. Also, SSMS can have a connection open to the DB by just having the object explorer open. Putting the database into single user mode can help. But you have a SPID open for the backup in your case. In general, the above steps work for most cases. – CRAFTY DBA Sep 21 '15 at 16:01
FWIW, when I ran sp_who2 I got the message "Database 'myDBName' is already open and can only have one user at a time." I tried a few more times and then results came back. Later, the issue repeated - and even after getting results back it then reverted to giving me the information message. – youcantryreachingme Apr 10 '19 at 02:07
That is - this solution would not work for me on SQL Server 2016. – youcantryreachingme Apr 10 '19 at 03:41
`First, make sure the object explorer is pointed to a system database like master.` How do you do this part? – Thick_propheT Nov 05 '22 at 00:27

Sathish · Answer 2 · 2016-08-30T09:53:57.197

72

First, find and KILL all the processes that have been currently running.

Then, run the following T-SQL to set the database in MULTI_USER mode.

USE master
GO
DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid > 50 AND dbid = DB_ID('<Your_DB_Name>')
EXEC(@kill);

GO
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

edited Aug 30 '16 at 09:53

answered Aug 26 '16 at 10:42

Sathish

1,936
4
28
38

This solution would not work for me on SQL Server 2016. If I query master..sysprocesses, I see several rows appear, but then the rows are replaced with an error message that the given database is in single user mode, etc. – youcantryreachingme Apr 10 '19 at 03:42
@youcantryreachingme, please provide the error message you were getting in SQL Server 2016 here so I/someone else would be able to help you get it resolved. – Sathish Apr 30 '19 at 09:43
the same as in the OP: Changes to the state or options of database 'my_db' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. – youcantryreachingme Apr 30 '19 at 13:38
When execute this script make sure your new query is not from the DB which is currently in single user mode. – Circle Hsiao Oct 01 '21 at 06:22
This worked for me in SQL 2016. Using @CRAFTYDBA's approach I couldn't kill the new SPID that initiate within moments and lock the DB again. This approach took care of it in one swoop. Thanks! – MJA Aug 08 '22 at 22:38

Tomas · Answer 3 · 2016-11-14T06:31:14.800

39

Right click your database in databases section
Select "Properties"
Select "Options" page
Scroll down "Other options" and alter "Restrict access" field

edited Nov 14 '16 at 06:31

answered Nov 14 '16 at 06:15

Tomas

1,377
3
17
32

4

This solution would not work for me on SQL Server 2016. Attempting to access properties shows an error that the database is in single user mode and already has a user connected. – youcantryreachingme Apr 10 '19 at 03:42
1

Worked for me on SQL Server 2017. SSMS asked whether I would want to close existing connections, which I did. Possibly a new feature. – Gustaf Liljegren Oct 07 '20 at 07:21
Also worked for me, SQL Server -v: 2012 – Jdslk Oct 14 '22 at 08:00
1

You have to close any query windows you might have open that could be attached to the database in question. It's not obvious that this is the problem, but if you run sp_who2 as suggested by other users, you'll be able to tell if there's still a query window hanging on. Make sure you select System Databases\master in Object Explorer before you open a new query window. – Thick_propheT Nov 05 '22 at 00:35

score 32 · Answer 4 · answered Sep 23 '13 at 18:14

32

To switch out of Single User mode, try:

ALTER DATABASE [my_db] SET MULTI_USER

To switch back to Single User mode, you can use:

ALTER DATABASE [my_db] SET SINGLE_USER

answered Sep 23 '13 at 18:14

rsbarro

27,021
9
71
75

1

I get the error: 'Changes to the state or options of database 'my_db' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.' – Liondancer Sep 23 '13 at 18:36
1

Can you stop and restart the database (obviously if this isn't a production system that will affect other users), and then retry the command? And as @CRAFTYDBA stated the command should be executed from the master database. – rsbarro Sep 23 '13 at 18:41
1

I expanded 'System Databases' and right clicked 'master' and selected 'New Query' and tried in both yours and @CRAFTYDBA's commamds. Same error =[ – Liondancer Sep 23 '13 at 18:46
1

Did you try stopping and restarting the database to kill the existing connection? You can also look here for more ways to kill connections to a database: http://stackoverflow.com/questions/11620/how-do-you-kill-all-current-connections-to-a-sql-server-2005-database – rsbarro Sep 23 '13 at 18:47
1

Yes I did. I just did it again to make sure I did just that haha – Liondancer Sep 23 '13 at 18:49
2

Right click on the server in the left pane and click 'Disconnect'. Make sure you have only one SSMS tab open on your database (right click and choose 'Disconnect other connections') and then execute the statement. Each tab and object explorer is a connection; you can only have one connection open to the database (hence, 'single-user mode'). Single user should be 'single connection' :) Good luck – tommy_o Sep 23 '13 at 22:42

Elanchezhian Narayanasamy · Answer 5 · 2014-07-11T06:29:17.503

19

I tried this is working

ALTER DATABASE dbName SET MULTI_USER WITH ROLLBACK IMMEDIATE

edited Jul 11 '14 at 06:29

answered Jul 09 '14 at 12:38

Elanchezhian Narayanasamy

1,214
1
17
31

score 11 · Answer 6 · answered Aug 22 '16 at 13:10

11

I had the same problem, and the session_id to kill was found using this query:

Select request_session_id From sys.dm_tran_locks Where resource_database_id=DB_ID('BI_DB_Rep');

answered Aug 22 '16 at 13:10

Geri Reshef

397
1
6
17

This was perfect. Found the pesky SPID and got the DB back online. Much thanks! – Russell Speight Jun 23 '17 at 22:54

score 10 · Answer 7 · edited Mar 21 '19 at 12:14

Just in case if someone stumbles onto this thread then here is a bullet proof solution to SQL Server stuck in SINGLE USER MODE

-- Get the process ID (spid) of the connection you need to kill
-- Replace 'DBName' with the actual name of the DB

SELECT sd.[name], sp.spid, sp.login_time, sp.loginame 
FROM sysprocesses sp 
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid  
WHERE sd.[name] = 'DBName'

As an alternative, you can also use the command “sp_who” to get the “spid” of the open connection:

-- Or use this SP instead

exec sp_who

-- Then Execute the following and replace the [spid] and [DBName] with correct values

KILL SpidToKillGoesHere
GO

SET DEADLOCK_PRIORITY HIGH
GO

ALTER DATABASE [DBName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

greg121 · Answer 8 · 2016-09-22T14:07:15.880

9

Press CTRL + 1

find the process that locks your database. Look in column dbname for your db and note the spid. Now you have to execute that statement:

kill <your spid>
ALTER DATABASE <your db> SET MULTI_USER;

edited Sep 22 '16 at 14:07

answered Sep 22 '16 at 14:00

greg121

914
1
11
19

CTRL + 1 is a very handy shortcut I didn't know about! – Tyler Forsythe Aug 07 '17 at 19:50

score 8 · Answer 9 · answered Oct 29 '15 at 10:12

8

The following worked for me:

USE [master]
SET DEADLOCK_PRIORITY HIGH
exec sp_dboption '[StuckDB]', 'single user', 'FALSE';
ALTER DATABASE [StuckDB] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [StuckDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE

answered Oct 29 '15 at 10:12

Jesper N

2,115
4
23
32

score 8 · Answer 10 · answered Oct 20 '16 at 11:32

8

Another option is to:

take the database offline; in SMSS, right click database and choose Take Offline, tick 'Drop all connections'
run ALTER DATABASE [Your_Db] SET MULTI_USER

answered Oct 20 '16 at 11:32

testpattern

2,382
1
25
29

Mine wouldn't let me take it offline, it just kept saying it was in single user mode and that a user was connected! (yes, I did tick "drop all connections"). I ended up detaching the database instead! – TabbyCool Mar 21 '17 at 12:09
I had to use this option as there were no users connected, and the KILL command wouldn't work on the sa connection. – Derek K Sep 06 '19 at 16:36

score 5 · Answer 11 · answered Mar 05 '14 at 01:43

Not sure if this helps anyone, but I had the same issue and could not find the process that was holding me up. I closed SSMS and stopped all the services hitting the local instance. Then once I went back in and ran the exec sp_who2, it showed me the culprit. I killed the process and was able to get the Multi_User to work, then restart the services. We had IIS hitting it every few minutes/seconds looking for certain packages.

score 5 · Answer 12 · edited Sep 28 '17 at 21:42

Adding to Jespers answer, to be even more effective:

SET DEADLOCK_PRIORITY 10;-- Be the top dog.

SET DEADLOCK_PRIORITY HIGH uses DEADLOCK_PRIORITY of 5.

What is happening is that the other processes get a crack at the database and, if your process has a lower DEADLOCK_PRIORITY, then it loses the race.

This obviates finding and killing the other spid (which might need to be done several times).

It is possible that you would need to run ALTER DATABASE more than once, (but Jesper does that). Modified code:

USE [master]
SET DEADLOCK_PRIORITY HIGH
exec sp_dboption '[StuckDB]', 'single user', 'FALSE';
ALTER DATABASE [StuckDB] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [StuckDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE

score 5 · Answer 13 · answered Oct 31 '17 at 06:24

5

Use this Script

exec sp_who

Find the dbname and spid column

now execute

kill spid 
go
ALTER DATABASE [DBName]
SET MULTI_USER;

answered Oct 31 '17 at 06:24

Aminur Rahman

400
1
6
14

score 3 · Answer 14 · answered May 12 '15 at 12:20

I ran across the same issue this morning. It turned out to be a simple issue. I had a query window open that was set to the single user database in the object explorer. The sp_who2 stored procedure did not show then connection. Once I closed it, I was able to set it to

score 2 · Answer 15 · answered Jan 04 '19 at 11:48

Today I faced the same issue where my database was changed from Multi User to Single User mode and this was eventually stopping me to publish database.

In order to fix this issue, I had to close all Visual Studio instances and run the below command in Sql Server query window -

USE [Your_Database_Name]; ALTER DATABASE [Your_Database_Name] SET MULTI_USER GO

This command has changed the DB from Single user to Multi User and afterwards, I was successfully able to publish.

score 1 · Answer 16 · answered Sep 15 '14 at 10:47

Even I come across same problem, not able to find active connections to my_db to kill it but still shows same error. I end up disconnecting all possible SSMS connections for any database on the Server, create a new connection from SSMS and change it to Multi user.

-- Actual Code to change my_db to multi user mode
USE MASTER;
GO
ALTER DATABASE [my_db] SET MULTI_USER

Note: This seems to be a possible bug in SQL Server 2005!

score 1 · Answer 17 · answered Apr 19 '16 at 16:49

We just experienced this in SQL 2012. A replication process jumped in when we killed the original session that set it to single user. But sp_who2 did not show that new process attached to the DB. Closing SSMS and re-opening then allowed us to see this process on the database and then we could kill it and switch to multi_user mode immediately and that worked.

I can't work out the logic behind this, but it does appear to be a bug in SSMS and is still manifesting itself in SQL 2012.

Arya Mohanan · Answer 18 · 2019-10-17T11:10:27.900

0

use master

GO

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid where d.name = 'database name'

kill 568 -- kill spid

ALTER DATABASE database name'

SET MULTI_USER go

edited Oct 17 '19 at 11:10

answered Oct 17 '19 at 11:01

Arya Mohanan

577
5
8

Exit single-user mode

18 Answers18

Linked

Related