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?

You can use the below sample t-sql code in order to list the top blocking processes in a MS SQL Server instance. But before executing the t-sql 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


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 stored procedure is getting the spid value of blocking processes and killing the related blocking processes (spid).
But the below stored procedure is actually not checking what is the context of the sql process, or not dealing with the reasons why the process is blocking other processes, just killing the process.


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



BlinkList   Del.icio.us   Digg   Furl   Simpy   Spurl   DZone   ma.gnolia   Shadows