title: SQL Server: Kill Process using Stored Procedure
body: <p>I want to modify the following as it doesn't seem to kill processes - I think its supposed to disconnect users (is this the same?). I want to be able to kill all process for a particular database - how can I modify the below:</p>
  
  <pre><code>create procedure [dbo].[sp_killusers](@database varchar(30))
  as
  ----------------------------------------------------
  -- * Created By David Wiseman, Updated 19/11/2006
  -- * http://www.wisesoft.co.uk
  -- * This procedure takes the name of a database as input
  -- * and uses the kill statment to disconnect them from
  -- * the database.
  -- * PLEASE USE WITH CAUTION!!
  -- * Usage:
  -- * exec sp_killusers 'databasename'
  ----------------------------------------------------
  set nocount on
  declare @spid int
  declare @killstatement nvarchar(10)
  
  -- Declare a cursor to select the users connected to the specified database
  declare c1 cursor for select request_session_id
                                  from sys.dm_tran_locks
                                      where resource_type='DATABASE'
                                      AND DB_NAME(resource_database_id) = @database
  open c1
  fetch next from c1 into @spid
  -- for each spid...
  while @@FETCH_STATUS = 0
  begin
        -- Don't kill the connection of the user executing this statement
        IF @@SPID &lt;&gt; @spid
        begin
              -- Construct dynamic sql to kill spid
              set @killstatement = 'KILL ' + cast(@spid as varchar(3))
              exec sp_executesql @killstatement
              -- Print killed spid
              print @spid
        end
        fetch next from c1 into @spid
  end
  -- Clean up
  close c1
  deallocate c1
  </code></pre>
  
  <h2>Update</h2>
  
  <p>The above doesn't work i.e. it doesn't kill the process.</p>
  
  <blockquote>
    <p>It doesn't kill the process. I look at
    the activity monitor and its still
    shows the process continuing and I can
    see my query still working in the
    query window. When I do "kill 53", the
    querying stops in the query window and
    the process is gone from the activity
    monitor! So th kill works but not this procedure why?</p>
  </blockquote>