SQL Server: Check online users of a database and kill the current sessions.

Scenario: Sometime restore/detach database you will get a message that the database cannot use exclusively because currently there are some users who using the database.

Find all the current users of your SQL Server.

SELECT login_name, COUNT(session_id) AS [session_count] 
FROM  sys.dm_exec_sessions
GROUP BY login_name
ORDER BY login_name


Then kill the current sessions.


USE master;
GO
ALTER DATABASE <your db Name>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE <your db Name>
SET MULTI_USER;

GO

Then try to continue your work as usual.



Have a nice day!!!

Mr. 221