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

database stuck in single user and no spid Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 4:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 5:56 AM
Points: 40, Visits: 277
Hi i have a dev database that is stuck in single user mode
when i try and access the database i can't a connection already in use.
However when i use exec sp_who - no connections are displayed against the database.

How can i get out of this please? As i would like to overwrite this database with the live database.
Post #1360080
Posted Monday, September 17, 2012 5:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 3, 2013 12:38 AM
Points: 91, Visits: 77
Try this and see if it fixes ur problem.

use Master
Go
select * from master.sys.sysprocesses
where spid>50 -- don't want system sessions
and dbid = DB_ID(name of database in question)

--Find the SPID which is making connection to your single_user mode DB and KIll it
Kill 100 -- replace 100 with the SPID which you get from the above query

ALTER database 'your DB name' set MULTI_USER with ROLLBACK IMMEDIATE





*******
Sudhakar
Post #1360104
Posted Monday, September 17, 2012 5:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 5:56 AM
Points: 40, Visits: 277
Sudhakar Vallamsetty (9/17/2012)
Try this and see if it fixes ur problem.

use Master
Go
select * from master.sys.sysprocesses
where spid>50 -- don't want system sessions
and dbid = DB_ID(name of database in question)

--Find the SPID which is making connection to your single_user mode DB and KIll it
Kill 100 -- replace 100 with the SPID which you get from the above query

ALTER database 'your DB name' set MULTI_USER with ROLLBACK IMMEDIATE

Hi Sudhakar,

I already tried this and there are no open connections listed against the database

Post #1360108
Posted Tuesday, September 18, 2012 3:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 1,610, Visits: 5,480
Are you using SQL Management Studio to issue these commands? It has an annoying tendency of opening multiple connections to the database, so you'd be better off using SQLCMD to test this out.
Post #1360653
Posted Tuesday, September 18, 2012 10:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 19, 2014 12:45 PM
Points: 120, Visits: 130
i hope you can also use "sp_dboptions '<DBName>' 'multiuser' 'true'"
Post #1360884
Posted Wednesday, September 19, 2012 1:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 5:56 AM
Points: 40, Visits: 277
in the end had to get someone to log into server with sys admin user and detach and then re-attach database.

Thanks for the tip about the management studio. Next time i will try sqlcmd
Post #1361162
Posted Friday, September 21, 2012 11:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, June 6, 2014 7:58 AM
Points: 1,837, Visits: 3,420
You can also see who is holding a lock on the database by querying sys.dm_tran_locks.

Kill the session_id and change to multi user by executing ALTER DATABASE db SET MULTI_USER.
Post #1362862
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse