Change the SQL database from Single_User mode to Multi_User mode

Recently I came across a situation, where one of our production database went to Single_User mode while moving the database to different LUN.

Below is the common steps to bring the database into Multi_user mode.

Method.1
------------------
ALTER DATABASE 'DBNAME'
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO


Method.2
-------------------
If query is not working, we can use the below one.

use master
GO

select
    d.name,     d.dbid,     spid,     login_time,     nt_domain,
    nt_username,     loginame
from sysprocesses p
    inner join sysdatabases d
        on p.dbid = d.dbid
where d.name = 'dbname'
GO

kill 56 => kill the number in spid field
GO

exec sp_dboption 'dbname', 'single user', 'FALSE'
GO




Method.3
---------------------
1.Take a backup
2. Create new database and restore the backup to it
3. Then Properties > Options > [Scroll down] State > RestrictAccess > select Multi_user and click OK
4. Delete the old database



Method.4  
----------------------

USE [master]
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [DBNAME] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [DBNAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE



Method 5
-----------------------------------------

1. Find out the Login which is accessing the database. 
2. Disable the Login\Revoke the permission temporarily. 
3. Execute the below query. 
ALTER DATABASE 'DBNAME'  SET MULTI_USER  WITH ROLLBACK IMMEDIATE 
GO 
4. Enable the Login\Give the required permission.