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
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