http://www.sqlservercentral.com/blogs/brian_kelley/2010/08/06/simple-script-to-determine-who-to-kill-spids/

Printed 2014/11/28 06:48AM

Simple Script to Determine Who To Kill (SPIDs)

2010/08/06

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.

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.