DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to find blocked processes?

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) 

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE