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
From there work out the db and kill the corresponding lock.
Switch from single to multi-user mode