One of the first issues I had when getting used to SSMS 2008 for the first time was not knowing how I could kill processes through the UI. One of my coworkers was kind enough to provide me the TSQL below to perform the task I had grown accustom to performing with a UI.
The TSQL:

DECLARE @p_SPID int, @p_SQL nvarchar(2000),@dbName nvarchar(100)

SET @dbName = 'NAME_OF_THE_DATABASE'
DECLARE #cur_Processes CURSOR FOR
   SELECT p.SPID
   FROM master.dbo.sysprocesses AS p
      JOIN master.dbo.sysdatabases AS d ON( d.dbid = p.dbid )
   WHERE d.Name = @dbName AND p.SPID > 50 -- (because spids of 50 or less are reserved for internal use.)
OPEN #cur_Processes
FETCH NEXT FROM #cur_Processes INTO @p_SPID
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @p_SQL = 'KILL ' + CONVERT( nvarchar(30), @p_SPID )
   PRINT @p_SQL
   EXECUTE( @p_SQL )
   FETCH NEXT FROM #cur_Processes INTO @p_SPID
END
CLOSE #cur_Processes
DEALLOCATE #cur_Processes
Advertisements