Something I learned today while looking at an old script. The script kills all SPIDs on a database. I had an instant urge to kill system SPIDs just to see what would happen. And the result was…
That’s a good thing though right? One less thing to be worrying about. You can’t kill system SPIDs.
There are many other facts and warnings in the Kill command’s documentation page. Here’s a few of them quickly.
You’re unable to kill your own SPID
You’ll see your SPID at the top of the query window next to the pin icon, or you can use @@SPID.
You can check THE PROGRESS OF a killed spids rollback
KILL 77 WITH STATUSONLY;
--This is the progress report.
spid 77: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 50 hours.
This won’t work for all the SPIDs you kill. For example, it doesn’t work for a 20 minute running delete statement has been killed and you’re sitting waiting for it to come back to life. It’s probably whatever sys.dm_exec_requests can pick up on, but don’t take my word for anything.
ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
HESITATE BEFORE THE KILL?
On the Microsoft documentation linked above it states that you should not kill processes with the following wait types:
I’ve yet to encounter issues as a result of a bad kill – when the occasional kill of a blocked or optimistic query does come along, it’s usually all good a few moments after the kill. Sometimes, perhaps there will be a calm 1 hour wait before a shrink db gets run too, who knows!