Checking for locks in MS SQL 2005

Ever needed to check any transactions being locked and then selecting from the master.dbo.sysprocesses or running sp_who2 active?

There is a stored proc I found on Tech Republic that will search for any locked/blocked processes and return them.
Ideal if you want to run a big transaction and then check for users, running locking transactions to shoot down with a kill command!

here’s the code:

CREATE PROCEDURE [dbo].[sp_locked]
AS
SELECT
	s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid), s.program_name, s.loginame, ObjectName = OBJECT_NAME(s.dbid), Definition = CAST(text AS VARCHAR(MAX))
INTO
	#Processes
FROM
	sys.sysprocesses s
		CROSS APPLY
	sys.dm_exec_sql_text (sql_handle)
WHERE
	s.spid > 50
WAITFOR DELAY '00:00:01';
WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
 AS
 (
		SELECT
			s.SPID, s.BlockingSPID, s.Definition,
			ROW_NUMBER() OVER(ORDER BY s.SPID),
			0 AS LevelRow
		FROM
			#Processes s
				JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
		WHERE
			s.BlockingSPID = 0
	UNION ALL
		SELECT
			r.SPID,  r.BlockingSPID, r.Definition,
			d.RowNo,
			d.LevelRow + 1
		FROM
			#Processes r
				JOIN Blocking d ON r.BlockingSPID = d.SPID
		WHERE
			r.BlockingSPID > 0
 )
SELECT * FROM Blocking ORDER BY RowNo, LevelRow
DROP TABLE #Processes

PLZ: Just to be safe, always check the details of the spid before pulling the trigger, to get more info like user, machine name, etc. use the master.dbo.sysprocesses table or sp_who2

Oja, Tech Republic is a awesome site for all things IT.
code taken from this guy

~ by Francois Wiid on February 15, 2008.