Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

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

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:

 

SELECT
    
'KILL ' + CONVERT(CHAR(3), spid) +
';'
  
,
status
  
,
hostname
  
,
program_name
  
,
loginame
FROM
sysprocesses
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.

 

Comments

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.