Category Archives: Blocking

Identifying blocking processes


Execute the following Microsoft SQL Server T-SQL scripts in the application database:

use AdventureWorks
select * from sys.sysprocesses where blocked != 0
— or
exec sp_who

Query and blocking information by spid:

— SQL Query information for current procecces

SELECT s.spid,

       BlockedBy = s.blocked,

       DBName = DB_NAME(s.dbid),

       ProgramName=s.program_name,

       LoginName=s.loginame,

       Query = CAST(TEXT AS VARCHAR(MAX))

FROM   sys.sysprocesses s

       CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE  s.spid > 50 — Exclude system processes

ORDER BY DBName, ProgramName

/* Partial results

select @@spid;  select SERVERPROPERTY(‘ProductLevel’); 

USE AdventureWorks2008;    DECLARE @ParmDefinition NVARCHAR(1024)

*/

— SQL find out source of blocking

SELECT SPID=p.spid,

       DBName = convert(CHAR(20),d.name),

       ProgramName = program_name,

       LoginName = convert(CHAR(20),l.name),

       HostName = convert(CHAR(20),hostname),

       Status = p.status,

       BlockedBy = p.blocked,

       LoginTime = login_time,

       QUERY = CAST(TEXT AS VARCHAR(MAX))

FROM   MASTER.dbo.sysprocesses p

       INNER JOIN MASTER.dbo.sysdatabases d

         ON p.dbid = d.dbid

       INNER JOIN MASTER.dbo.syslogins l

         ON p.sid = l.sid

       CROSS APPLY sys.dm_exec_sql_text(sql_handle)

WHERE  p.blocked = 0

       AND EXISTS (SELECT 1

                   FROM   MASTER..sysprocesses p1

                   WHERE  p1.blocked = p.spid)

/*

 SPID DBName      ProgramName LoginName   HostName    Status      BlockedBy   LoginTime      QUERY

84    AdventureWorks2008  Microsoft SQL Server Management Studio – Query                                                                                        SERVER\ksmith     DELLSTAR          sleeping                            0     2010-03-29 00:33:17.890   BEGIN TRANSACTION   insert test values (99) 

*/

%d bloggers like this: