Breaking News: Grepper is joining You.com. Read the official announcement!

SQL Script to get blocking tree

Youssef Lteif answered on December 16, 2020 Popularity 1/10 Helpfulness 1/10

answer SQL Script to get blocking tree

related cpanel

SQL Script to get blocking tree

Comment

/*  SQL Blocking Tree w/Cursor info
Thanks SQL Server Expert JOHNNYBNO 
*/
IF OBJECT_ID('tempdb..#Blocks') IS NOT NULL
    DROP TABLE #Blocks
SELECT   spid
        ,blocked
        ,REPLACE (REPLACE (st.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS batch
INTO     #Blocks
FROM     sys.sysprocesses spr
	CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
GO
 
WITH BlockingTree (spid, blocking_spid, [level], batch)
AS
(
    SELECT   blc.spid
            ,blc.blocked
            ,CAST (REPLICATE ('0', 4-LEN (CAST (blc.spid AS VARCHAR))) + CAST (blc.spid AS VARCHAR) AS VARCHAR (1000)) AS [level]
            ,blc.batch
    FROM    #Blocks blc
    WHERE   (blc.blocked = 0 OR blc.blocked = SPID) AND
			EXISTS (SELECT * FROM #Blocks blc2 WHERE blc2.BLOCKED = blc.SPID AND blc2.BLOCKED <> blc2.SPID)
    UNION ALL
    SELECT   blc.spid
            ,blc.blocked
            ,CAST(bt.[level] + RIGHT (CAST ((1000 + blc.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS [level]
            ,blc.batch
    FROM     #Blocks AS blc
		INNER JOIN BlockingTree bt 
			ON	blc.blocked = bt.SPID
    WHERE   blc.blocked > 0 AND
			blc.blocked <> blc.SPID
)
SELECT	N'' + ISNULL(REPLICATE (N'|         ', LEN (LEVEL)/4 - 2),'')
        + CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN '' ELSE '|------  ' END
        + CAST (bt.SPID AS NVARCHAR (10)) AS BlockingTree
        ,spr.lastwaittype   AS [Type]
        ,spr.loginame       AS [Login Name]
        ,DB_NAME(spr.dbid)  AS [Source database]
        ,st.text            AS [SQL Text]
		,CASE WHEN cur.sql_handle IS NULL THEN '' ELSE (SELECT [TEXT] FROM sys.dm_exec_sql_text (cur.sql_handle)) END  AS [Cursor SQL Text]
        ,DB_NAME(sli.rsc_dbid)  AS [Database]
		,OBJECT_SCHEMA_NAME(sli.rsc_objid,sli.rsc_dbid) AS [Schema]
		,OBJECT_NAME(sli.rsc_objid, sli.rsc_dbid) AS [Table]
        ,spr.waitresource   AS [Wait Resource]
        ,spr.cmd            AS [Command]
        ,spr.program_name   AS [Application]
        ,spr.hostname       AS [HostName]
        ,spr.last_batch     AS [Last Batch Time]
FROM BlockingTree bt
	LEFT OUTER JOIN sys.sysprocesses spr 
		ON	spr.spid = bt.spid
	CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
	LEFT JOIN sys.dm_exec_cursors(0) cur
		ON	cur.session_id = spr.spid AND
			cur.fetch_status != 0
	JOIN sys.syslockinfo sli
		ON	sli.req_spid = spr.spid AND
			sli.rsc_type = 5 AND
			OBJECT_NAME(sli.rsc_objid, sli.rsc_dbid) IS NOT NULL
ORDER BY LEVEL ASC

Popularity 1/10 Helpfulness 1/10 Language sql

Source: blog.sqlauthority.com

Tags: blocking get script sql tree

Link to this answer
Share Copy Link

Contributed on Nov 26 2024

Youssef Lteif

0 Answers Avg Quality 2/10

Closely Related Answers

cpanel

Comment

cPanel is a web hosting control panel.

You can use it to easily manage websites, email addresses, databases,
domains, store files and more, with the intent of making web hosting easy
for the end user all from a website.

It has integrations with billing systems such as WHMCS and blesta to
make it easy for hosting providers to provide web hosting.

It is developed by cPanel LLC,

Popularity 10/10 Helpfulness 2/10 Language whatever

Source: Grepper

Tags: cpanel cp

Link to this answer
Share Copy Link

Contributed on Dec 16 2020

Quantum

0 Answers Avg Quality 2/10

SQL Script to get blocking tree

Contents

More Related Answers

SQL Script to get blocking tree

Closely Related Answers

cpanel

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.