Remove a Database from Single User Mode

Hey everybody! Welcome back to SQL Something.

It’s been a long week but still trying to stick to my self-imposed rule of having one post per week. Here’s a simple one on how to remove Single User Mode from a DB.

Fig. 1: What a DB looks like in Single User Mode in Management Studio.

 

From my readings and my experience it seems a DB could go into single user mode in order to prevent corruption after some form of incorrect action was taken, whether purposefully or not. This recently happened to me when I tried to purposefully break replication. I tried to delete a DB while it was publishing, it then threw an error and curled up in a fetal position in single user mode. Poor little guy.

So how do we take a DB out of Single User Mode? We can try the following:

• Exec sp_dboption 'database_name', 'single user', 'FALSE'

You may initially get an error like: Changes to the state or options of database 'rb_data_services_mscrm' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

This is because some other process is currently using the one session the DB is allowing. You will have to find the process ID and KILL it. There are a couple ways you can go about this.

Find Process ID, Solution 1

You can use the sp_who or sp_who2 (gives additional info) command to list all current DB connections as follows:

USE master 
exec sp_who --can use sp_who2 here instead to display more info

If you have a lot of connections however, the list might become a bit overwhelming. In order to filter the results to only a specific DB you can use the following:

DECLARE @Table TABLE(

        SPID INT,

        Status VARCHAR(MAX),

        LOGIN VARCHAR(MAX),

        HostName VARCHAR(MAX),

        BlkBy VARCHAR(MAX),

        DBName VARCHAR(MAX),

        Command VARCHAR(MAX),

        CPUTime INT,

        DiskIO INT,

        LastBatch VARCHAR(MAX),

        ProgramName VARCHAR(MAX),

        SPID_1 INT,

        REQUESTID INT

)

INSERT INTO @Table EXEC sp_who2

SELECT  *

FROM    @Table

WHERE DBName = 'Test_DB’

You should get results like:

Fig. 2: SP_WHO2 results.

Find Process ID, Solution 2

An alternative we can use is as follows:

select spid AS 'SPID', d.dbid AS 'Database ID', d.name AS 'Database Name', login_time, nt_domain, nt_username, loginame

from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid

where d.name = 'Test_Db'

You should get results like this:

Fig. 3: Custom Query Results

Both queries get the job done. What I will do is see which one takes less time to run and indicate in an update.

KILL the Process

We can finally get around to KILL-ing the process now that we have identified its SPID.

Run the following:

KILL SPID -- where SPID is the actual SPID number, example: KILL 51

Now that the process is dead we can retry the first query:

Exec sp_dboption 'database_name', 'single user', 'FALSE'

Hope this post helps someone. :-)

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)