SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Simple Script to Determine Who To Kill (SPIDs)

I'm mostly working now supporting the building of SSIS packages for ETL. And on one particular project we've been making heavy use of snapshots to facilitate easy rollbacks when we detect an error in the way we've handled the process. Well, restoring a database snapshot requires kicking everyone out of the database. And since I'm primarily using T-SQL to do the restore, I needed a quick script to kill the appropriate SPIDs before running the restore. But I want to make sure I know who I'm killing and that they aren't doing anything substantial. Enter my new best friend:


'KILL ' + CONVERT(CHAR(3), spid) +
WHERE dbid = DB_ID('MyDatabase'

This generates the KILL statements for me and I can copy then into a new query window and execute. I don't have to copy 'em all, for instance, I don't copy the background processes you sometimes see running. Also, if I see a status that suggests someone is active, I can investigate further and postpone the rollback until I determine either (a) they are done or (b) what they are doing doesn't have as high a priority as the rollback.


K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by Jason Brimhall on 6 August 2010

Nice script.

It's nice being an assassin.  We get to kill things in our line of work - and don't break any laws doing it.

Leave a Comment

Please register or log in to leave a comment.