SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

Identify and Kill Top Blocking Processes In MS SQL Server

If you experience blocking issues on your production MS SQL Server databases, then you frequently need to identify the top blocking processes that are causing the blocking issues.
For a SQL Server programmer the first monitoring place for defining blocking processes in MS SQL Server is the sysprocesses view in master database.
If you are a SQL Server database administrator (DBA) then you can also use the Activity Monitor. But for this article, we are not dealing with the MS SQL Server Activity Monitor to summarize or monitor processes, resources, I/O status, etc. on Microsoft SQL Server database instance.
Let's see what we can do using T-SQL codes to list processes and spid 's that are causing blocking problems on the sql server databases.

Which Process is the Top Blocking Process?

SQL developers or database administrators can use the below sample T-SQL code to list top blocking processes in a MS SQL Server instance. Before executing SQL Select query, you should switch to the master SQL Server database.

use master
GO
select * from sysprocesses (nolock) where blocked = 0 and spid in (
  select blocked from sysprocesses (nolock) where blocked <> 0
)
GO
Code

Kill Blocking Process Automatically using SQL Code

After defining the user process running on the SQL Server instance, you can kill if you are required to kill that sql process by running the Kill @spid T-SQL command.

The below SQL stored procedure is getting the process id, spid value of blocking processes and then following SQL codes kill the related blocking processes (spid).
Please be aware that below SQL Server stored procedure is not checking what is the context of the sql process. It does not deal with the reasons why SQL process is blocking other processes. Following codes just kill SQL process to remove blocking situation for other user 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
Code


SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.