Aşağıdaki procedure ile sistemde meydana gelebilecek uzun süreli Lock durumlarında, kilitlenmeye neden olan kullanıcı ve tablo bilgisini kolayca elde edebilirsiniz. "Mode" kolonunda "X" olan satır kilitlenmeye neden olan oturuma aittir. Sorgu kopyalandıktan sonra ‹ › karakterleri yerine  query editöründeki orjinal karakterler kullanılmalıdır.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [dbo].[ERP_Locks]

as

set nocount on

select DISTINCT

sp.hostname HostName,

sp.program_name "Prg Name",

sp.hostprocess "PID",

sp.nt_domain "DOMAIN",

sp.nt_username "Usr Name",

sp.net_address "Net Addr",

sp.loginame "Login",

convert (smallint, l1.req_spid) As spid,

l1.rsc_dbid As dbid,

(SELECT name from master.dbo.sysdatabases (nolock) where dbid = l1.rsc_dbid) As Db,

object_name(l1.rsc_objid) As ObjName,

(select name from sysobjects (nolock) where id = l1.rsc_objid) As Obj,

l1.rsc_indid As IndId,

sp.sql_handle as sql_handle,

substring (v.name, 1, 4) As Type,

substring (l1.rsc_text, 1, 16) as Resource,

substring (u.name, 1, 8) As Mode,

substring (x.name, 1, 5) As Status

from master.dbo.syslockinfo l1,

master.dbo.syslockinfo l2,

master.dbo.spt_values v,

master.dbo.spt_values x,

master.dbo.spt_values u,

master.dbo.sysprocesses sp

where

l1.rsc_type = v.number

and v.type = 'LR'

and l1.req_status = x.number

and x.type = 'LS'

and l1.req_mode + 1 = u.number

and u.type = 'L'

and l1.rsc_type ‹› 2

and l1.rsc_dbid = l2.rsc_dbid

and l1.rsc_bin = l2.rsc_bin

and l1.rsc_objid = l2.rsc_objid

and l1.rsc_indid = l2.rsc_indid

and l1.req_spid ‹› l2.req_spid

and l1.req_status ‹› l2.req_status

and sp.spid = l1.req_spid

order by

substring (l1.rsc_text, 1, 16),

substring (x.name, 1, 5)

return (0)