Never put the DB into Single user Mode using GUI Management Studio in SQL Server

Never put the DB into Single user Mode using GUI Management Studio in SQL Server

How to put the db in single user mode:

http://www.manage-sqlserver.blogspot.com/2012/08/kill-all-existing-user-connections-in.html

Explanation:

If we put the DB into single user mode using GUI, as shown below

This particular window prompt of database properties will craete a session (SPID) and change the mode of database.

When you say ok, that prompt will get closed by moving the db state from multi user to single user.

Suppose the SPID which made the db into single user mode is 58.

Once you close the prompt , SPID 58 will not be visible in SP_who2. But it will be still active in sleeping mode.It will not get terminated.

As it is not shown in SP_WHO2, SQL Server will give one more SPID for that session even though it is in single user mode.

Mean while in that time window, if any application get connected to the database...they will get the connection and being DBA you are left with no connection on that database.

One SPID (58) which is responsible for Multi user to single user is not visible.
One visible SPID (say 59) is with Application.

we can see that details in :

select * from sys.dm_tran_locks where resource_database_id = db_id ('dbname')
Go

Kill the Application connected ID, and get connected to change the database mode.

Hope the above information helps

Thanks