SQLServerCentral.com / SQL Server 2008 / SQL Server 2008 Administration / Need a blocking script with specific details / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 18:43:07 GMT20RE: Need a blocking script with specific detailshttp://www.sqlservercentral.com/Forums/Topic1476657-1550-1.aspxOk, this is not all that pretty, but it at least returns the right values. I'm sure there's a more efficient way of acquiring them, though. It creates a temporary table that eventually contains a row for each Blocking SPID/Blocked SPID combination, including "indirect" blocks, and then counts them per blocking SPID. Hopefully something like this (or a more efficient suggestion from someone else) will help you get what you want.[code="sql"]SELECT Blocked AS Blocker, spid as Blocked INTO #BlockedSpids FROM master.dbo.sysprocessesWHERE blocked&gt;0WHILE EXISTS (SELECT Headblocker.Blocker,Tailblocked.Blocked FROM #BlockedSpids AS HeadBlockerinner join #BlockedSpids AS TailblockedON Headblocker.Blocked=Tailblocked.blockerWHERE NOT EXISTS (SELECT * FROM #BlockedSpids AS cmpr WHERE Headblocker.blocker=cmpr.blocker and Tailblocked.blocked=cmpr.blocked))BEGININSERT into #BlockedSpidsSELECT distinct Headblocker.Blocker,Tailblocked.Blocked FROM #BlockedSpids AS HeadBlockerinner join #BlockedSpids AS TailblockedON Headblocker.Blocked=Tailblocked.blockerWHERE NOT EXISTS (SELECT * FROM #BlockedSpids AS cmpr WHERE Headblocker.blocker=cmpr.blocker and Tailblocked.blocked=cmpr.blocked)ENDSELECT Blocker, COUNT(Blocked) AS AllBlocked FROM #BlockedSpidsGROUP BY Blocker[/code]I hope this helps!Thu, 01 Aug 2013 11:42:58 GMTJacob WilkinsRE: Need a blocking script with specific detailshttp://www.sqlservercentral.com/Forums/Topic1476657-1550-1.aspxHi All, This is the query I have SELECT [Head Blocker SPID] = s.session_id, [Number of Statements Blocked] = count(r2.blocking_session_id), [Head Blocker SQL Statement] =(SELECT text AS [text()]FROM sys.dm_exec_sql_text(p.sql_handle)FOR XML PATH(''), TYPE), [Head Blocker Run Time (seconds)] = (datediff(second, s.last_request_start_time, getdate())), [Source Host Name] = ISNULL(s.host_name, N''), [Login Name] = s.login_name FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)LEFT OUTER JOIN( SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_numFROM sys.dm_os_waiting_tasks) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)WHERE r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) GROUP BY s.session_id,s.login_name,s.host_name,s.last_request_start_time,p.sql_handleCan somebody please help me with one thing.. in this query I am able to get all the spids that are "DIRECTLY" being blocked. But I need to tweak it in a way that this will give me all the spids which are directly or indirectly blocked for eg – If SPID is blocking SPIDs 56,74 and 56 is blocking 83,34 then the last column(No. of statements it is blocking directly or indirectly) should give ‘4’. Thanks in advance. VJWed, 31 Jul 2013 04:49:09 GMTgvijaybabuRE: Need a blocking script with specific detailshttp://www.sqlservercentral.com/Forums/Topic1476657-1550-1.aspxPretty much all of what you're looking for is available in sys.dm_exec_requests dynamic management view. Take a look there.Wed, 24 Jul 2013 05:03:50 GMTGrant FritcheyRE: Need a blocking script with specific detailshttp://www.sqlservercentral.com/Forums/Topic1476657-1550-1.aspxyou can check where blocked != 0 from master..sysprocesses table.you can also use profiler now to trap information on blocks.Tue, 23 Jul 2013 10:37:26 GMTgeorge sibbaldRE: Need a blocking script with specific detailshttp://www.sqlservercentral.com/Forums/Topic1476657-1550-1.aspxPost what you have so far so we can see what you're doing.Tue, 23 Jul 2013 10:20:46 GMTs_osborne2Need a blocking script with specific detailshttp://www.sqlservercentral.com/Forums/Topic1476657-1550-1.aspxHello All, I am looking for a script that will give me these details. 1) spid2) Total number of spids it is blocking directly or indirectly. 3) blocking Query4) Login name who ran the Query. 5) how long the query has been running for. I am able to get all the info except "total numberof SPIDs it is blocking directly or indirectly"Can somebody please help me with this, that will be very helpful. thanks in advance. regardsVBTue, 23 Jul 2013 09:23:53 GMTgvijaybabu