How to kill all sessions that have open connection in a SQL Server, How to Kill SQL Threads or Kill User SessionsHow To Kill All Users In Specific Database On SQL Server, sql - Script to kill all connections to a database, sql server - Kill all sessions for ONE user in MS SQL - Database, SQL SERVER - Quickest Way to - Kill All Threads, SQL Server – Kill all sessions using database, Sql Server: Find and Kill Specific User Session
SQL Puzzle | How to Kill all other existing SQL server connection except the login started by current session
Write a script to Kill all other existing SQL server connection except the login started by current session
- The challenge is to do it with T-SQL statements only.
- Add your solution(s) in the comments section or send you solution(s) to firstname.lastname@example.org
SOLUTION – 1
-- DECLARE @Id AS VARCHAR(MAX)='' IF OBJECT_ID('tempdb..#TempBills') IS NOT NULL DROP TABLE #TempBills SELECT CAST(SPID as VARCHAR(10)) Id INTO #TempBills FROM MASTER.dbo.sysprocesses sp CROSS APPLY ( SELECT * FROM MASTER.dbo.sysdatabases sd WHERE sp.dbid = sd.dbid AND nt_username <> 'SQLSERVERAGENT' AND nt_username <> '' )l WHILE EXISTS (SELECT TOP 1 Id FROM #TempBills) BEGIN SELECT TOP 1 @Id = Id FROM #TempBills EXEC( 'KILL ' + @Id ) DELETE FROM #TempBills WHERE Id = @Id END --
[ Note- Al though I don’t prefer this command. You can use it as per your need. Also be careful when run it on the pre production and production environment. ]
Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.
Enjoy !!! Keep Learning