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 ?

StevieB
  • 153
  • 1
  • 4
  • 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

5 Answers5

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]
Erik Dekker
  • 76
  • 3
  • 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
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.

Neil Knight
  • 207
  • 3
  • 10
  • 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. –  Jun 29 '11 at 09:50
  • Connect using `Dedicated Admin Connection` http://msdn.microsoft.com/en-us/library/ms178068(v=SQL.90).aspx – Neil Knight Jun 29 '11 at 09:52
  • Hey Neil no idea what that is, can you explain ? –  Jun 29 '11 at 09:53
  • @StevieB: I've added a link to my last comment. – Neil Knight Jun 29 '11 at 09: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. –  Jun 29 '11 at 09:59
  • I connected to Database Engine Query, but in the dropdown of database I simply cant select the database I want to open. This single user crap is officially the worst thing I ever encountered in SQL Server –  Jun 29 '11 at 10:02
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.

joeqwerty
  • 109,901
  • 6
  • 81
  • 172
  • 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 '11 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

Neil Knight
  • 207
  • 3
  • 10
Dee Yu
  • 88
  • 6
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.

Bobby
  • 11
  • 1