|
Execute the following
Microsoft SQL Server T-SQL scripts to obtain currently exectuting process and blocking information:
use master;
-- List all blocked processes
select * from sys.sysprocesses where blocked != 0
-- List all processes
exec sp_who
-- or
select * from sys.sysprocesses order by spid
------------
Related Article:
How to monitor blocking in SQL Server 2005 and in SQL Server 2000
Query and blocking information by spid:
-- SQL Query information for current processes
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 - blocking processes
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)
*/ |
