Menu & Search
More on SPIDs… and Killing Them

More on SPIDs… and Killing Them


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

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
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION


HESITATE BEFORE THE KILL?

On the Microsoft documentation linked above it states that you should not kill processes with the following wait types:

AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SIGNAL HANDLER

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!