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

User · Answer

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

User · Answer

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

User · Answer

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   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

User · Answer

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

User · Answer

Use this Script  exec sp who   Find the dbname and spid column  now execute   kill spid  go ALTER DATABASE  DBName  SET MULTI USER

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I tried this is working   ALTER DATABASE dbName SET MULTI USER WITH ROLLBACK IMMEDIATE

User · Answer

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

User · Answer

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  gt  50 AND dbid   DB ID   lt Your DB Name gt    EXEC  kill    GO SET DEADLOCK PRIORITY HIGH ALTER DATABASE   lt Your DB Name gt   SET MULTI USER WITH NO WAIT ALTER DATABASE   lt Your DB Name gt   SET MULTI USER WITH ROLLBACK IMMEDIATE GO

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt your spid gt  ALTER DATABASE  lt your db gt  SET MULTI USER

User · Answer

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

User · Answer

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

[sql-server] Exit single-user mode

Examples related to sql-server

Examples related to sql-server-2008-r2