4 Steps to take a SQL SERVER Database out of Single User Mode

Recently my database got stuck in single user mode and I could not open it to change into multi-user.

Steps 1-4 will show you how it works.  Step 5 shows how to do it with one script

 I used the scripts below to fix this.
This is in SQL Server management studio with SQL Server R2

1. Find My Database id
    SELECT db_id('MyDB')



2. Find the session id that was using the single user mode, using the dbid returned from step 1 

    SELECT SPID,DBID,*
  FROM SYSPROCESSES
  WHERE DBID NOT IN(1,2,3,4) 
  AND SPID>50 
  AND SPID<>@@spid 
  AND dbid=[DBID]



3.Kill the session using the SPID from step 2

   KILL 999

4.put the database back into multi-user mode
   ALTER DATABASE MyDatabase
   SET MULTI_USER

Now you can get back into your database, 

       

Below is a script to do it all at once.
DECLARE @DBID AS INT
DECLARE @SPID AS INT
DECLARE @SQL AS NVARCHAR(MAX)

SELECT @SPID=SPID
FROM SYSPROCESSES
WHERE DBID NOT IN(1,2,3,4) AND SPID>50 AND SPID<>@@spid AND dbid=db_id('MyDatabase')

IF @SPID!=NULL
BEGIN
    SELECT 'Nothing to delete'
END
ELSE
BEGIN
    SET @SQL='KILL '+CONVERT(VARCHAR,@SPID)
   
    EXEC sp_executesql @SQL
END

ALTER DATABASE MyDatabase
SET MULTI_USER


To Format this TSQL I Used http://www.ubitsoft.com/products/t-sql-beautifier/ it worked great