Dangers of running this stored procedure that kill blocking processes



  • use master
    GO
    CREATE PROC support_KillBlockingProcesses (@RecursiveCount int = NULL)
    AS
    
    DECLARE @count int, @spid int, @sql nvarchar(max)
    SET @count = ISNULL(@RecursiveCount, 3)
    
    while @count > 0
    begin
    
     begin try
      set @spid = (
       select top 1 spid from sysprocesses (nolock)
       where blocked = 0 and spid in (
        select blocked from sysprocesses (nolock) where blocked <> 0
       )
      )
      if @spid > 50
      begin
       set @sql = N'kill ' + cast(@spid as nvarchar(100))
       exec sp_executesql @sql
       --print @sql
      end
     end try
     begin catch
      --print 'error'
     end catch
    
     set @count = @count - 1
    end
    GO
    

    I saw this stored procedure, and I am wondering if there's any danger of running this stored process whenever the db is frozen. My thought process is that you still need to kill the blocking process even if the process you kill is important, so is there really any consequence of doing this? And what's the point of knowing which process is freezing up the db?



  • Blocking doesn't "freeze up the db". It causes those who happen to be blocked to be ...blocked until the one holding the lock that causes the blocking situation to release that lock. That is not the same thing as "freezing up the db".

    The danger of that procedure it that kills any blocker. The one that it kills might be a very transient blocker, where the blocking situation would only happen for 100 ms, but you happened to run that proc just those 100 ms. So, you killed somebody without gaining anything, since the real problem wasn't taken care of. And you now run it again, with the risk of doing this yet again.

    Don't just kill anybody who happen to block somebody else. Kill with care!




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2