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)
| < Prev |
|---|
