Show_Locks

,

Shows database, table names which are locked, the user who locks them, the command which is executed

CREATE   PROC HPSP_UT_ShowLocks

	AS
	SET ANSI_NULLS OFF 
	SET NOCOUNT ON

	create table #Locks(
	     spid	 	int       
	,    dbid 		int
	,   ObjId	int     
	,   IndId	int
	,   Type		varchar(4)
	,   Resource	varchar(16)
	,   Mode	varchar(8)
	,   Status            varchar(5)           
		)                 

	INSERT          #Locks
	EXEC                sp_lock


	SELECT DISTINCT p.spid,  p.status, ObjId, o.name TblName,  --p.ecid,
				   RTRIM(nt_username) username,
				   RTRIM(loginame)  loginame,
				   hostname ,
				CONVERT(char(5),blocked)  blk, 
				  CASE
								when p.dbid = 0 then null
								when p.dbid <> 0 then db_name(p.dbid)
				end   dbname 
				   ,cmd
	
	 FROM master.dbo.sysprocesses p
		  JOIN 	(SELECT DISTINCT spid,  dbid, ObjId, Mode FROM       #Locks      
				WHERE      Mode in('X', 'IX', 'SIX')       
			)  l
			on p.spid = l.spid
			and p.dbid = l.dbid
		JOIN dbo.sysobjects o
			on o.id = ObjId
	

	drop table #Locks

Rate

5 (1)

Share

Share

Rate

5 (1)