Share via

SQL Server Administration: finding blocking process with procedure

  • Article

https://msdnshared.blob.core.windows.net/media/2016/05/0640_NinjaAwardTinyGold.pngGold Award Winner

When one process blocks another from accessing an object, it's often because yet another process is blocking it. The best tool for the sleuth in this case isn't a magnifying glass or meerschaum pipe—it's a stored procedure that traces process blocks back to their originators.

That's what sp_find_ root_blocker does. Here's the code:

01.USE master
02.GO
03.IF OBJECT_ID('sp_find_root_blocker') IS NOT NULL
04. DROP PROC sp_find_root_blocker
05.GO
06.CREATE PROCEDURE sp_find_root_blocker @help char(2)=NULL
07.AS
08.IF (@help='/?') GOTO Help
09.IF EXISTS (SELECT * FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON
10.(p1.spid=p2.blocked)) BEGIN
11. DECLARE @spid int
12. SELECT @spid=p1.spid -- Get the _last_ prime offender
13. FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON
14.(p1.spid=p2.blocked)
15. WHERE p1.blocked=0
16. SELECT p1.spid,
17. p1.status,
18. loginame=LEFT(p1.loginame,20),
19. hostname=substring(p1.hostname,1,20),
20. blk=CONVERT(char(3),p1.blocked),
21. db=LEFT(db_name(p1.dbid),10),
22. p1.cmd,
23. p1.waittype
24. FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON
25.(p1.spid=p2.blocked)
26. WHERE p1.blocked=0
27. RETURN(@spid) -- Return the last root blocker
28.END ELSE BEGIN
29. PRINT 'No processes are currently blocking others.'
30. RETURN(0)
31.END 
32.RETURN 0
33.Help:
34.EXEC sp_usage @objectname='sp_find_root_blocker', @desc='Finds the root
35.offender(s) in
36.the chain(s) of blocked processes',
37.@parameters='', @returns='spid of the root blocking process (returns the last one
38.if
39.there are multiple)',
40.@author='Technet Guru', @email='msdn.com',
41.@version='6', @revision='0',
42.@datecreated='20060101', @datelastchanged='20161212',
43.@example='sp_find_root_blocker'
44.RETURN -1
45.GO

Testing our procedure:

sp_find_root_blocker 
 
------------
 
No processes are currently blocking others.

The result was a success:
No processes  blocking