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’re unable to kill your own SPID.
# You can check the progress of a killed SPIDs rollback.
# Microsoft’s warning when killing SPIDs.
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!
Leave a Reply