5

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 ?

1
  • 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, 2011 at 16:34

5 Answers 5

5

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]
1
  • 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, 2011 at 14:04
4

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.

6
  • 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, 2011 at 9:50
  • Connect using Dedicated Admin Connection msdn.microsoft.com/en-us/library/ms178068(v=SQL.90).aspx Jun 29, 2011 at 9:52
  • Hey Neil no idea what that is, can you explain ?
    – StevieB
    Jun 29, 2011 at 9:53
  • @StevieB: I've added a link to my last comment. Jun 29, 2011 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, 2011 at 9:59
1

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.

1
  • 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.
    – Shane
    Jun 30, 2011 at 17:14
1

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

1

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.

2
  • That's really the same thing as the accepted and second highest voted answers...
    – Chris S
    Feb 7, 2012 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. Jul 16, 2012 at 8:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .