Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

finding the blocks in sql server

By Paarthasarathy, 2007/09/09

Hai buddies,

The following procedure can be used to find whether there are any blocks in the sql server.The procedure if run in Master database , can be used like any other
system stored procedure from any database.
The output of the proc will contain the following

SPID OF RUNNING APPLICATION
APPLICATION NAME
EXECUTING DATA
BLOCKED (Y/N)
BLOCKING SPID
BLOCKING APPLICATION
EXECUTING DATA (BLOCKING APPLICATION)

The Procedure's target version is SQL SERVER 2000.
If u have something to say on this, pls leave ur comments either in my blogspot or mail me.

CREATE PROC SP_WHAT
AS
BEGIN
/*CODE WRITTEN BY
PAARTHASARATHY K
http://InayathilEnthanGeetham.blogspot.com/
http://PaarthasarathyK.blogspot.com/
*/
/*TO FIND THE SPID'S THAT ARE ACTIVE AND WHAT THEY ARE EXECUTING*/
CREATE TABLE #T1
(
A SYSNAME,
B SYSNAME,
[INPUT_BUFFER] NVARCHAR(1200),[SPID] INT NULL
);
DECLARE @A NVARCHAR(400),@B INT

DECLARE SP_IDS CURSOR
FOR SELECT SPID FROM SYSPROCESSES
WHERE STATUS ='RUNNABLE'
--STATUS <> 'SLEEPING'AND
--STATUS <> 'BACKGROUND'
AND SPID <> @@SPID
AND PROGRAM_NAME <> 'SQL PROFILER'
UNION ALL
SELECT SPID FROM SYSPROCESSES
WHERE BLOCKED <>0


OPEN SP_IDS
FETCH NEXT FROM SP_IDS INTO @B

WHILE @@FETCH_STATUS =0
BEGIN

SET @A = 'DBCC INPUTBUFFER('+RTRIM(@B)+')WITH NO_INFOMSGS;';


INSERT #T1(A,B,[INPUT_BUFFER]) EXEC SP_EXECUTESQL @A

UPDATE #T1 SET [SPID] = @B WHERE [SPID] IS NULL
FETCH NEXT FROM SP_IDS INTO @B
END


CLOSE SP_IDS
DEALLOCATE SP_IDS

CREATE TABLE #T2
(
SPID INT,
PROGRAM_NAME SYSNAME,
INPUT_BUFFER NVARCHAR(1200),
BLOCKED VARCHAR(2),
BLOCKING_PROCESS VARCHAR(10),
PROGRAM_NAME1 SYSNAME,
INPUT_BUFFER1 NVARCHAR(1200)
)


INSERT INTO #T2
SELECT A.SPID,B.PROGRAM_NAME, A.INPUT_BUFFER ,
CASE B.BLOCKED WHEN 0 THEN 'N' ELSE 'Y' END ,
CASE WHEN B.BLOCKED <>0 THEN CONVERT(VARCHAR(10),B.BLOCKED) ELSE 'NO BLOCK' END ,
'',''
FROM #T1 A,
MASTER..SYSPROCESSES B
WHERE A.SPID = B.SPID


UPDATE A
SET A.PROGRAM_NAME1 = B.PROGRAM_NAME
FROM #T2 A ,
SYSPROCESSES B
WHERE A.BLOCKING_PROCESS = B.SPID
AND A.BLOCKED = 'Y'



CREATE TABLE #T3
(
A SYSNAME,
B SYSNAME,
[INPUT_BUFFER] NVARCHAR(1200),[SPID] INT NULL
);

DECLARE CURSOR_2 CURSOR
FOR SELECT BLOCKING_PROCESS FROM #T2
WHERE BLOCKED ='Y'


OPEN CURSOR_2
FETCH NEXT FROM CURSOR_2 INTO @B

WHILE @@FETCH_STATUS =0
BEGIN

SET @A = 'DBCC INPUTBUFFER('+RTRIM(@B)+')WITH NO_INFOMSGS;';


INSERT #T3(A,B,[INPUT_BUFFER]) EXEC SP_EXECUTESQL @A

UPDATE #T3 SET [SPID] = @B WHERE [SPID] IS NULL
FETCH NEXT FROM CURSOR_2 INTO @B
END

CLOSE CURSOR_2
DEALLOCATE CURSOR_2
/*
The Procedure's target version is SQL SERVER 2000.
If u have something to say on this, pls leave ur comments either in my blogspot or mail me.
*/
UPDATE A
SET A.INPUT_BUFFER1 = B.INPUT_BUFFER
FROM #T2 A, #T3 B
WHERE A.BLOCKING_PROCESS <> 'NO BLOCK' AND
A.BLOCKING_PROCESS = CONVERT (VARCHAR(10),B.SPID)

SELECT DISTINCT LTRIM(RTRIM(SPID)) 'SPID'
,LTRIM(RTRIM(PROGRAM_NAME)) 'APPLICATION NAME',
LTRIM(RTRIM(INPUT_BUFFER)) 'EXECUTING DATA',
LTRIM(RTRIM(BLOCKED)) 'BLOCKED (Y/N)',
LTRIM(RTRIM(BLOCKING_PROCESS)) 'BLOCKING SPID' ,
LTRIM(RTRIM(PROGRAM_NAME1)) 'BLOCKING APPLICATION',
LTRIM(RTRIM(INPUT_BUFFER1)) 'EXECUTING DATA (BLOCKING APPLICATION)' FROM #T2


DROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3

END