How to kill all sessions in SQL Server

In my previous two blogs, I talked about how kill a session in SQL Server and how to kill multiple sessions in SQL Server. In this blog I will talk about how to kill all sessions of database in SQL Server.

The blog doesn’t promote this as best practice and would recommend not running it on production server until necessary.

USE master;
GO
-- setting database in single user mode
-- kill all sessions
ALTER DATABASE AdventureWorks2014
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

-- change back to multi_user for 
-- database to accept new connections
ALTER DATABASE AdventureWorks2014
SET MULTI_USER;
GO

The above query changes database user mode to singe_user which automatically kills all database sessions. The query then changes database user mode to multi_user allowing new database connections.

   

 
Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

   

Leave a Reply

Your email address will not be published.