Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Hey currently have a database stuck in single mode after setting it to single mode to attempt to try take if offline which I wish I never did....

I tried

ALTER DATABASE DB SET MULTI_USER

but get

Msg 5064, Level 16, State 1, Line 1 Changes to the state or options of database 'DB' 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.

When I right click on Database to go to i.e. properties I get

Database "DB" is already open and can only have one user at a time.

Any Ideas ?

share|improve this question

migrated from stackoverflow.com Jun 29 '11 at 10:04

This question came from our site for professional and enthusiast programmers.

    
Time would be better spent by just spending the 30 minutes it takes to backup, uninstall, and then re-install SQL2005 again. Trying to figure out this rare issue would take FAR longer. SQL2005 can be re-installed twice as quick as SQL2008. –  djangofan Jun 30 '11 at 16:34
add comment

5 Answers

up vote 5 down vote accepted

SELECT * from sys.databases --> get database_id from the database.

SELECT * FROM MASTER..SysProcesses WHERE dbid = [thedbid] (insert your db id)

Look up the spid to kill

KILL [theid]
share|improve this answer
    
when I try and do this second line SELECT * FROM MASTER..SysProcesses WHERE dbid = [thedbid] I get Msg 924, Level 14, State 1, Line 2 Database 'DB' is already open and can only have one user at a time. –  StevieB Jun 30 '11 at 14:04
add comment

Use SP_Who to find the session ID of the active connection to the database.

Use KILL <session id> to terminate the connection.

Then do the Alter Database command you tried.

share|improve this answer
    
I ran SP_WHO and I get Msg 924, Level 14, State 1, Procedure sp_who, Line 79 Database 'DB' is already open and can only have one user at a time. –  StevieB Jun 29 '11 at 9:50
    
Connect using Dedicated Admin Connection msdn.microsoft.com/en-us/library/ms178068(v=SQL.90).aspx –  Neil Knight Jun 29 '11 at 9:52
    
Hey Neil no idea what that is, can you explain ? –  StevieB Jun 29 '11 at 9:53
    
@StevieB: I've added a link to my last comment. –  Neil Knight Jun 29 '11 at 9:54
    
Cheers, I just dont see where this step can be done is In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE. –  StevieB Jun 29 '11 at 9:59
show 1 more comment

At the very worst, if no other method works, you should be able to reboot the server to kill any connection to the database and then put it in multiuser mode after the reboot.

share|improve this answer
    
I agree with this. Somehow you lost your single connection and something else obtained it. If any web apps or anything at all accesses the SQL Server it will have locked you out. Restart the SQL service or at worst the server itself. –  Sean Howat Jun 30 '11 at 17:14
add comment

On the same query window that you tried the ALTER DATABASE DB SET MULTI_USER, try... ALTER DATABASE DB SET MULTI_USER WITH ROLLBACK IMMEDIATE

share|improve this answer
add comment

I got the same thing when trying to go from Single User to Offline mode. An obscure problem with a (possible) obscure solution:

  1. Run the query below. It should give you the "Single User" error. select * from sys.SysProcesses

  2. Switch over to the "Results" pane. Identify the LAST spid in the list.

  3. Let's say the last SPID in the results pane to be displayed is 70. Kill the next spid. KILL 71

That freed up the connection for me. I guess that the sys.sysprocesses query returns results until it hits a database it can't connect to.

share|improve this answer
    
That's really the same thing as the accepted and second highest voted answers... –  Chris S Feb 7 '12 at 16:00
    
No it's not the same thing. Bobby says that if the results give you x, y and z, you should run KILL z+1. –  Martin Ørding-Thomsen Jul 16 '12 at 8:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.