SQL2005 Stuck in Single Mode User

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 ?

Solution 1:

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]

Solution 2:

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.

Solution 3:

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.

Solution 4:

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