再送一个汉化过的,希望对你有用CREATE procedure tool_sp_lock2 --- 2002/11/24 00:00 @spid1 int = NULL, /* server process id to check for locks */ @spid2 int = NULL /* other process id to check for locks */ asset nocount on /* ** Show the locks for both parameters. */create table #t (spid varchar(100), dbid varchar(100), objid varchar(100), indid varchar(100), type varchar(100), resource varchar(100), mode varchar(100), status varchar(100) )if @spid1 is not NULL begin insert into #t (spid, dbid, objid, indid, type, resource, mode, status) select convert (smallint, req_spid) As spid, rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, substring (v.name, 1, 4) As Type, substring (rsc_text, 1, 16) as Resource, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR' and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS' and master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L' and req_spid in (@spid1, @spid2) end/* ** No parameters, so show all the locks. */ else begin insert into #t (spid, dbid, objid, indid, type, resource, mode, status) select convert (smallint, req_spid) As spid, rsc_dbid As dbid, rsc_objid As ObjId, rsc_indid As IndId, substring (v.name, 1, 4) As Type, substring (rsc_text, 1, 16) as Resource, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR' and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS' and master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L' order by spid endselect spid, dbid, db_name(dbid) as '数据库', Objid, object_name(objid) as '对象名称', indid, object_name(indid) as '索引名称', type, case type when 'DB' then '数据库' when 'FIL' then '文件' when 'IDX' then '索引' when 'PAG' then '页面' when 'KEY' then '索引键值' when 'TAB' then '表' when 'TEXT' then '区域' when 'RID' then '行标志号' end as '资源标志', resource, mode, case upper(mode) when 'S' then '共享锁' when 'X' then '排它锁' when 'IS' then '意向锁' when 'IX' then '意向排它锁' when 'SIX' then '共享意向排它锁' when 'SCH-S' then '调度稳定性锁' when 'SCH-M' then '调度修改锁' when 'BU' then '批量更新锁' end as '锁定模式', status, case status when 'GRANT' then '锁定状态' when 'WAIT' then '等待状态' when 'CNVRT' then '转换状态' end as '请求状态' from #treturn (0) -- sp_lock2GO
sp_who --> sp_who2
sp_lock
都用了吗?
再送一个汉化过的,希望对你有用CREATE procedure tool_sp_lock2 --- 2002/11/24 00:00
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to check for locks */
asset nocount on
/*
** Show the locks for both parameters.
*/create table #t
(spid varchar(100),
dbid varchar(100),
objid varchar(100),
indid varchar(100),
type varchar(100),
resource varchar(100),
mode varchar(100),
status varchar(100)
)if @spid1 is not NULL
begin
insert into #t
(spid, dbid, objid, indid, type,
resource, mode, status)
select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L' and req_spid in (@spid1, @spid2)
end/*
** No parameters, so show all the locks.
*/
else
begin
insert into #t
(spid, dbid, objid, indid, type,
resource, mode, status)
select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 16) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
order by spid
endselect spid,
dbid,
db_name(dbid) as '数据库',
Objid,
object_name(objid) as '对象名称',
indid,
object_name(indid) as '索引名称',
type,
case type
when 'DB' then '数据库'
when 'FIL' then '文件'
when 'IDX' then '索引'
when 'PAG' then '页面'
when 'KEY' then '索引键值'
when 'TAB' then '表'
when 'TEXT' then '区域'
when 'RID' then '行标志号'
end as '资源标志',
resource,
mode,
case upper(mode)
when 'S' then '共享锁'
when 'X' then '排它锁'
when 'IS' then '意向锁'
when 'IX' then '意向排它锁'
when 'SIX' then '共享意向排它锁'
when 'SCH-S' then '调度稳定性锁'
when 'SCH-M' then '调度修改锁'
when 'BU' then '批量更新锁'
end as '锁定模式',
status,
case status
when 'GRANT' then '锁定状态'
when 'WAIT' then '等待状态'
when 'CNVRT' then '转换状态'
end as '请求状态'
from #treturn (0) -- sp_lock2GO
1、用事件探查器跟踪
2、sp_who
3、sp_lock是三种方法
你也可以查询 master..syslockinfo,看看那个表被锁住了。
kill