While looking at an old script today, one that kills SPIDs on a SQL Server database… I had an instant urge to try kill a 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, and here’s a few other tests I’ve done;
You’ll see your SPID at the top of the query window next to the pin icon, or you can use @@SPID.
KILL 55 WITH STATUSONLY; GO /* 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
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!