Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
Thanks. It turned out that i had to run sp_who. Then I had to kill all connections to the master database made by the person who restored the database. After that I ran ALTER DATABASE [mydb] SET MULTI_USER WITH NO_WAIT and it worked. –  Martin Ørding-Thomsen Jul 16 '12 at 9:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.