Skip to Content

SQL Server database administrators frequently need in especially development and test environments to find and kill blocked process. Following scripts are useful in that scenario.

–Find All the Blocked Processes

SELECT

spid,

status,

loginame=SUBSTRING(loginame,1,12),

hostname=SUBSTRING(hostname,1, 12),

blk = CONVERT(char(3), blocked),

dbname=SUBSTRING(DB_NAME(dbid),1, 10),

cmd,

waittype

FROM master.dbo.sysprocesses

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

–Kill all the Blocked Processes of a Database


DECLARE @DatabaseName nvarchar(50)

Set the Database Name

SET @DatabaseName = N’Datbase_Name’

Select the current Daatbase

SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)

SET @SQL =

SELECT @SQL = @SQL + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

and spid IN (SELECT blocked FROM master.dbo.sysprocesses)

–You can see the kill Processes ID

–SELECT @SQL

–Kill the Processes

EXEC(@SQL)

Or You can use the following script

— Script to kill all blocked processes

declare @max_count int, @count int, @sqlstring varchar(100)

declare @spid_table table (spid int NOT NULL)

INSERT @spid_table

select spid

from master.dbo.sysprocesses

where spid in (select blocked from master.dbo.sysprocesses where blocked <> 0) and blocked = 0

select @max_count = MAX(spid) FROM @spid_table

select top 1 @count = spid from @spid_table

while @count <= @max_count

begin

select @sqlstring = ‘kill ‘ + CONVERT(varchar(4), @count)

exec(@sqlstring)

print @sqlstring

IF @count = @max_count

begin

break

end

ELSE

BEGIN

select top 1 @count = spid FROM @spid_table where spid > @count

end

end

Similarly DBA can kill all the Processes of a Database as


— Kill all the Processes of a Database

DECLARE @DatabaseName nvarchar(50)

–Set the Database Name

SET @DatabaseName = N’Datbase_Name’

Select the current Daatbase

SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)

SET @SQL =

SELECT @SQL = @SQL + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

–You can see the kill Processes ID

–SELECT @SQL

–Kill the Processes

EXEC(@SQL)

Identify the blocking query


SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

 

-- Script to view all current processes / sessions on the server

 

select * from master.dbo.sysprocesses

Regards,
Marcelo Silva Santo
To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply