Database stuck in single user mode

Question

Database stuck in single user mode

j.a.c

SSCrazy

Points: 2283
More actions
June 23, 2009 at 5:19 pm

#132185

Hi,
Suppose you had a database stuck in single user mode that is in a busy OLTP environment.
Given that:
1. The connection that originally put the database into single user mode is gone.
2. The DAC has not been set up on that instance.
3. It is almost guaranteed that if you kill the spid holding the only connection to the database, another connection will grab it in milliseconds.
How would you get the db back into multi-user mode?
Thanks,
Andy

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 1

You shouldn't need to be in the database to change to multiple users. Connect to the master database as a system administrator and then alter the properties of the database.

j.a.c SSCrazy Points: 2283 More actions · Answer 2

But another SPID already has the only available connection to that database. Running the alter database command from master even logged in as a sysadmin would not work.

Msg 5064, Level 16, State 1, Line 1

Changes to the state or options of database 'test' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 3

LOL, that kind of defeats the purpose of connecting to master.

I think the only thing you can likely do here, is connect as admin, then pause the server. That prevents new connections. Once it's paused, kill the connection in that database, and you ought to be able to get int. You should be able to then restart the server.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Something like this is likely to work

use master

kill 75 -- the connection to the database in single user mode

use [MyDatabase]

alter database [MyDatabase] set multi_user with rollback immediate

If that doesn't work, try this:

use master

kill 75 -- the connection to the database in single user mode

alter database [MyDatabase] set offline with rollback immediate

alter database [MyDatabase] set online, multi_user with rollback immediate

Another thing you could do is disable the logins that have access to the database, kill the active database connection, and then alter the database.

jimmie.roberts SSC Veteran Points: 298 More actions · Answer 5

Hi, I'm having this exact problem. How do you identify the connection that is consuming the single-user connection? Is it 75 by default?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Query sysprocesses and see what connection is connected to that database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

sysprocesses is a server-level view, it's not database-specific. It returns all of the sessions connected to the server and, as one of the columns, has the id of the database that the session is currently connected to.

select * from master.sys.sysprocesses

where spid>50 -- don't want system sessions

and dbid = DB_ID(<name of database in question>)

I'm using sysprocesses (a compatibility view) because I don't know how to get the DBID of a sleeping connection via the 2005 DMVs

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jimmie.roberts SSC Veteran Points: 298 More actions · Answer 8

Thanks for the quick reply. But the problem still is I cannot login to the SQL db at all because it's in Single-user mode, so how can I execute a SQL select statement if I cannot login???

If I could execute a select statement to look at the sysprocesses I would be able to execute the Alter database command to set it to Multi-User mode.

It's driving me crazy!

Jimmie

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

jimmie.roberts (6/25/2009)
Thanks for the quick reply. But the problem still is I cannot login to the SQL db at all because it's in Single-user mode, so how can I execute a SQL select statement if I cannot login???

You don't need to connect to that database. Run it from any database on that instance. Run it from master. sysprocesses is a server-wide view, you'll get exactly the same results from it no matter what database you run it from.

If I could execute a select statement to look at the sysprocesses I would be able to execute the Alter database command to set it to Multi-User mode.

No. If that was the case I wouldn't be telling you to query sysprocesses. All you need to query sysprocesses is a connection to a database, any database. Master's fine. So all you need to be able to do is log into the instance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jimmie.roberts SSC Veteran Points: 298 More actions · Answer 10

I use SQL Studio and then Object Explorer to navigate thru SQL. Right after I excuted this command:

ALTER DATABASE TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE

I could see in Object Explorer that the TEMP DB was in Single-User mode. But, when I right clicked the db to perform a task, (a "force" restore), I got an error saying that I couldn't access the TEMP DB. I closed SQL Studio and then restarted it and now when it tries to connect to the Instance, I get an error that says login failed for user 'sqladmin' Cannot open default database. I am configured to connect via Windows Authentication but my username is greyed-out (I cannot modify it) and the password field is blank. I try connecting via SQL authentication and that fails also.

That ALTER DATABASE command really messed me up.

Do you know how I can modify who I am logging in as?

Thanks,

Jimmie

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

In the management studio login dialog, click on Options (bottom right). That will take you to another dialog where you can change the default database. Pick master.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jimmie.roberts SSC Veteran Points: 298 More actions · Answer 12

Thank you! That was it. Duh.

Now, I can go into the properties of my DBs and then I click on Options and I can set the Restrict Access setting to Multi-User!

That's it! Thank you again.

Jimmie

Abhijit More SSCertifiable Points: 6897 More actions · Answer 13

You can identity it by running the sysprocesses query.

When you run the query you will receive the same message saying "database is in single_user mode".

If you check the resultset you will get some spid see the last spid add one in that you will get the spid accessing the single_user database.

Kill the spid

I hope this works.

Abhijit - http://abhijitmore.wordpress.com

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 14

Divya Agrawal

Hall of Fame

Points: 3144

December 22, 2009 at 10:34 am

#1095531

you can execute sp_who2 to see the processes

--Divya