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)
2 Comments. Leave new
But killing processes is important part of admin’s world. Have this situation when there are apps consuming a lot of data for instance with unlimited date ranges on really big TBs databases we have. We as DBAs educate devs to write optimized queries and put there some boundaries especially for very complex realtime calculations in OLTP systems. But management says no there won’t be any issue, put it into production, we have big HW it will handle it. So you have to let it pass into production, there starts to be performance issues and management comes to you saying, ok you were right, kill it and we will reimplement it and replace it. But first you have to let it go because sometimes management doesn’t listen to technical arguments and simply they think they are right because they are managers and you can do nothing about it. This is the real life and that is why killing all app’s related SPIDs comes handy sometimes.
I a similar light as Jan posted, as a DBA I find many runaway queries… in those cases I only kill the one SPID(s)… I rarely kill all.
Now, in the event I am forced to kill all SPIDS connecting to a specific database, say in a ROLLBACK situation after a deployment goes bad; I have found that using the SINGLE_USER can backfire very quickly if an application is connecting in the background using a privileged user (in some cases “sa”). In a situation like that you have successfully locked yourself out of the database until someone can bring down the application… and in my experience, good luck with that!
To overcome this issue, I use:
ALTER DATABASE <> SET OFFLINE WITH ROLLBACK IMMEDIATE;
WAITFOR DELAY ’00:00:30′; — 30 seconds is usually enough to force a timeout on any application, but you can set to your desired number or remove altogether if you just want to perform a quick “reset”.
ALTER DATABASE <> SET ONLINE;
… rest of your code…
This way… all connections will be guaranteed to disconnect regardless of privilege.
Kind Regards