SQL Server Administration: finding blocking process with procedure

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.

you can see this article in msdn wiki page too

http://social.technet.microsoft.com/wiki/contents/articles/33344.sql-server-administration-finding-blocking-process-with-procedure.aspx