SQL Server 2008 R2 database stuck in single user mode

sql-server-2008

We tried to restore a database, and it failed for some reason. Now it is stuck in single user mode. Here is what I tried:

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 = 'mydb'

It returned one result and I killed it using kill 77. I read somewhere that you should also kill the id plus one for good measure, so we also ran kill 78. At this point, running the query again returned 0 connections.

I've tried all of these:

exec sp_dboption 'mydb', 'single user', 'FALSE';

SET DEADLOCK_PRIORITY HIGH
exec sp_dboption 'mydb', 'single user', 'FALSE';

ALTER DATABASE [mydb] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [mydb] SET MULTI_USER WITH ROLLBACK IMMEDIATE

They all give me

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

I realize that I could probably solve the issue by restarting the sql service or perhaps rebooting the server. However, this is a production server, so I'd prefer to avoid it.

Any suggestions?

Best Answer

Take a quick look at the SPID

USE master 
EXEC sp_who

From there work out the db and kill the corresponding lock.

Switch from single to multi-user mode

ALTER DATABASE 'DB NAME' SET MULTI_USER WITH ROLLBACK IMMEDIATE

Related Topic