Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Database stuck in single user mode Expand / Collapse
Author
Message
Posted Tuesday, June 23, 2009 5:19 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:43 AM
Points: 586, Visits: 3,622
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
Post #740655
Posted Tuesday, June 23, 2009 5:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:55 PM
Points: 31,278, Visits: 15,736
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
Post #740661
Posted Tuesday, June 23, 2009 5:56 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:43 AM
Points: 586, Visits: 3,622
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.
Post #740664
Posted Tuesday, June 23, 2009 6:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:55 PM
Points: 31,278, Visits: 15,736
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
Post #740668
Posted Tuesday, June 23, 2009 10:23 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 11:06 PM
Points: 3,109, Visits: 11,514
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.
Post #740740
Posted Thursday, June 25, 2009 2:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2009 4:10 PM
Points: 4, 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
Post #742176
Posted Thursday, June 25, 2009 2:24 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
Query sysprocesses and see what connection is connected to that database.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #742181
Posted Thursday, June 25, 2009 2:40 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #742195
Posted Thursday, June 25, 2009 2:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2009 4:10 PM
Points: 4, 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
Post #742198
Posted Thursday, June 25, 2009 3:07 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
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

Post #742227
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse