SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database stuck in single user mode


Database stuck in single user mode

Author
Message
j.a.c
j.a.c
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 4473
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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62004 Visits: 19101
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
j.a.c
j.a.c
Right there with Babe
Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)Right there with Babe (717 reputation)

Group: General Forum Members
Points: 717 Visits: 4473
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
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62004 Visits: 19101
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5704 Visits: 11771
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
jimmie.roberts
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 4
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86733 Visits: 45254
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86733 Visits: 45254
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>Wink



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
jimmie.roberts
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 4
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86733 Visits: 45254
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search