How to get database out of single user

Question

Hi

SQL Server 2005. Had changed database to single user/read only option. Now I cant change database back to multi user and all commands are getting stuck.

Msg 924, Level 14, State 1, Line 1
Database 'db1' is already open and can only have one user at a time.

I have tried all below and failed to get around this issue

sp_dboption db1, 'single', false

alter database db1 multi_user with rollback immediate

I cannot select the spid with as I get Msg 924 with below commands which all get stuck.

select spid from sysprocesses where dbid = (id of database)

sp_who does not show

What can be done to get out of this as this is a production server and difficult to restart midweek.

Answer 1

I'd close down your connection, re-open it, run sp_who against the server hosting the database, then execute 'KILL <spid>' when you find the connection that's got the db open.  Once killed, you can run yuor alter database db1 mult_user statement.

Let us know how you get on...

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 2

Andrew

Its not my connection which is connected to the database. The issue is cannot find the spid which is connected to kill it as all selects from system tables generate the Msg 924 error

Answer 3

you're saying even sp_who generates a 924 error?  or just that it doesn't show the spid connected to the database?

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 4

Or use DAC (Dedicated Admin Connection). Make sure you have enabled it first

In SSMS type in admin:<instanceName> for Server Name

after connecting to master ALTER DATABASE <DBName> SET MULTI_USER

Answer 5

I had a similar problem when the Query Tab for the single user got closed accidently.

1. I logged in through DAC. I tried killing a SPID (sp_who2) that I thought had the lock but could not get access to the database and I could not bring the database to muti-user mode.

2. I logged on as sa, but I could not bring the database to muti-user mode.

3. I tried re-starting the SQL Server instance (Test Server), the instance would not stop.

4. I had to reboot the Database Server (Test Server). The database recovered on it's own.

I was then able to bring the database to single user mode, ran check db and then brought the database back to multi-user mode.

-Jeelani

Answer 6

Thansk for the replies. sp_who/2 generates results but does not show the spid which is connected to the database and teminates with the Msg 924.

I will try the DAC method. Thanks