Question: How to Kill User Sessions (SPID) in SQL Server?
Answer: Before I answer this question, I need to ask one more question – “Why do you want to kill all of the user sessions in your SQL Server?”
Honestly, I do not see any valid business logic to kill all the SPID in the SQL Server. So here my question to you – why would you want to kill all the user sessions on your SQL Server?
If you say you want to kill all the sessions because your server was running 100% of resources, well that is not a good answer because killing processes which is using your resources and doing what we have asked them to do is never a good idea. Anyway, I see the question to kill user sessions again and again in the interview and there are quite a few different answers I see out there.
Here is the script which I have built to kill all of your user session id.
DECLARE @sqlstring NVARCHAR(max)='' SELECT @sqlstring = @sqlstring + 'KILL ' + CAST(spid AS VARCHAR(40)) + ';' FROM sys.sysprocesses WHERE spid NOT IN (@@spid) -- Exclude current session AND spid > 50 -- Exclude system spid PRINT @sqlstring EXEC sp_executesql @sqlstring
If you only want to access your system and want to disallow others to access the system, I suggest that you set your database in a single user mode. Here is blog post discussing the same SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
Let me know your thoughts in the comment sections.
Reference: Pinal Dave (https://blog.sqlauthority.com)