create procedure [dbo].[sp_who_lock] as begin declare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter intcreate table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)IF @@ERROR<>0 RETURN @@ERRORinsert into #tmp_lock_who(spid,bl) select 0 ,blocked from (select * from master..sysprocesses where blocked>0 ) a where not exists(select * from (select * from master..sysprocesses where blocked>0 ) b where a.blocked=spid) union select spid,blocked from master..sysprocesses where blocked>0IF @@ERROR<>0 RETURN @@ERROR-- 找到临时表的记录数 select @intCountProperties = Count(*),@intCounter = 1 from #tmp_lock_whoIF @@ERROR<>0 RETURN @@ERRORif @intCountProperties=0 select '现在没有阻塞和死锁信息' as message-- 循环开始 while @intCounter <= @intCountProperties begin -- 取第一条记录 select @spid = spid,@bl = bl from #tmp_lock_who where id = @intCounter begin if @spid =0 select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' else select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' DBCC INPUTBUFFER (@bl ) end-- 循环指针下移 set @intCounter = @intCounter + 1 end drop table #tmp_lock_whoreturn 0 end
查看阻塞的语句 declare Roy_lock cursor for select db_name(dbid),0,blocked from master..sysprocesses a where Blocked>0 and not exists(select 1 from Master..Sysprocesses where blocked=a.spid) union select db_name(dbid),spid,blocked from master..sysprocesses a where Blocked>0 declare @DBName sysname,@spid bigint,@Blocked bigint open Roy_lock fetch next from Roy_lock into @DBName,@spid,@Blocked while @@fetch_status=0 begin if @spid=0 print N'鎖定數據庫:'+@DBName+' 語句:' else print N'鎖定數據庫:'+@DBName+' 進程SPID:'+rtrim(@spid)+' 語句:' dbcc inputbuffer(@Blocked) fetch next from Roy_lock into @DBName,@spid,@Blocked end close Roy_lock deallocate Roy_lock GO
--查阻塞 with Lock(level, dbName,spid,blocked,sql_handle) as (select 0 as level, db_name(dbid),spid,blocked,sql_handle from master..sysprocesses a where blocked > 0 union all select level + 1 as Level, db_name(a.dbid),a.spid,a.blocked,a.sql_handle from master..sysprocesses a join lock b on a.spid = b.blocked where level < 5) select distinct * from Lock a cross apply sys.dm_exec_sql_text(a.sql_handle)b
不好意思,上面的代码只适合于SQL Server 2005以后的版本。
2k -- Sysprocesses
貌似都不是看阻塞时间的嘛?能给一个看正在阻塞进程从开始到现在阻塞了多少时间的SQL吗?
--查看数据库阻塞 select a.resource_type, case a.resource_type when 'RID' then '行锁(表没有聚集索引表)' when 'KEY' then '行锁(表有聚集索引表)' when 'Page' then '数据页锁' when 'EXTEnt' then '连续8个页面集合锁' when 'Hobt' then '堆或平衡树锁(表没有聚集索引)' when 'OBJECT' then '表锁' when 'application' then '应用程序锁' else '其他' end as '其他锁', case when a.resource_type ='OBJECT' then object_name(a.resource_associated_entity_id) when a.resource_associated_entity_id is null or a.resource_associated_entity_id=0 then null else object_name(p.[object_id]) end as '对象', db_name(a.resource_database_id) as '数据库', a.request_mode '请求锁模式', case a.request_mode when 'S' then '共享' when 'X' then '排他' when 'U' then '更新' when 'IS' then '意向共享' when 'IU' then '意向更新' when 'IX' then '意向排他' when 'SIX' then '意向排他共享' when 'SIU' then '意向更新共享' when 'UIX' then '更新意向排他' when 'shc-s' then '架构稳定' when 'Sch-m' then '架构修改' when 'BU' then '大容量更新' else '其他' end as '锁模式', a.request_type as '事物上锁对象', case a.request_status when 'grant' then '锁已赋予' when 'convert' then '锁正在转换' else '等待中' end as '等待赋予锁', a.request_owner_type as '请求所有者的实体类型', b.session_id as '被封锁会话', b.blocking_session_id as '源锁会话', b.wait_type as '等待类型', c.[host_name] as '主机名称', c.[login_name] as '登录名', c.[status] as '状态'
from sys.dm_tran_locks a join sys.dm_os_waiting_tasks b on b.resource_address = a.lock_owner_address join sys.dm_exec_sessions c on c.[session_id]=b.[session_id] left join sys.partitions as p on a.resource_associated_entity_id=p.partition_id --54指锁定资源的进程ID号,假设查出来的SPID为54,52 dbcc inputbuffer(54) dbcc inputbuffer(52)
create procedure [dbo].[sp_who_lock]
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter intcreate table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)IF @@ERROR<>0 RETURN @@ERRORinsert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from master..sysprocesses where blocked>0 ) a
where not exists(select * from (select * from master..sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from master..sysprocesses where blocked>0IF @@ERROR<>0 RETURN @@ERROR-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_whoIF @@ERROR<>0 RETURN @@ERRORif @intCountProperties=0
select '现在没有阻塞和死锁信息' as message-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_whoreturn 0
end
declare Roy_lock cursor for
select
db_name(dbid),0,blocked
from
master..sysprocesses a
where
Blocked>0 and
not exists(select 1 from Master..Sysprocesses where blocked=a.spid)
union
select
db_name(dbid),spid,blocked
from
master..sysprocesses a
where
Blocked>0
declare @DBName sysname,@spid bigint,@Blocked bigint
open Roy_lock
fetch next from Roy_lock into @DBName,@spid,@Blocked
while @@fetch_status=0
begin
if @spid=0
print N'鎖定數據庫:'+@DBName+' 語句:'
else
print N'鎖定數據庫:'+@DBName+' 進程SPID:'+rtrim(@spid)+' 語句:'
dbcc inputbuffer(@Blocked)
fetch next from Roy_lock into @DBName,@spid,@Blocked
end
close Roy_lock
deallocate Roy_lock
GO
with Lock(level, dbName,spid,blocked,sql_handle)
as
(select 0 as level, db_name(dbid),spid,blocked,sql_handle from master..sysprocesses a where blocked > 0
union all
select level + 1 as Level, db_name(a.dbid),a.spid,a.blocked,a.sql_handle from master..sysprocesses a join lock b on a.spid = b.blocked
where level < 5)
select distinct * from Lock a cross apply sys.dm_exec_sql_text(a.sql_handle)b
select a.resource_type,
case a.resource_type
when 'RID' then '行锁(表没有聚集索引表)'
when 'KEY' then '行锁(表有聚集索引表)'
when 'Page' then '数据页锁'
when 'EXTEnt' then '连续8个页面集合锁'
when 'Hobt' then '堆或平衡树锁(表没有聚集索引)'
when 'OBJECT' then '表锁'
when 'application' then '应用程序锁'
else '其他' end as '其他锁',
case when a.resource_type ='OBJECT' then object_name(a.resource_associated_entity_id)
when a.resource_associated_entity_id is null or a.resource_associated_entity_id=0 then null
else object_name(p.[object_id]) end as '对象',
db_name(a.resource_database_id) as '数据库',
a.request_mode '请求锁模式',
case a.request_mode
when 'S' then '共享'
when 'X' then '排他'
when 'U' then '更新'
when 'IS' then '意向共享'
when 'IU' then '意向更新'
when 'IX' then '意向排他'
when 'SIX' then '意向排他共享'
when 'SIU' then '意向更新共享'
when 'UIX' then '更新意向排他'
when 'shc-s' then '架构稳定'
when 'Sch-m' then '架构修改'
when 'BU' then '大容量更新'
else '其他' end as '锁模式',
a.request_type as '事物上锁对象',
case a.request_status
when 'grant' then '锁已赋予'
when 'convert' then '锁正在转换'
else '等待中' end as '等待赋予锁',
a.request_owner_type as '请求所有者的实体类型',
b.session_id as '被封锁会话',
b.blocking_session_id as '源锁会话',
b.wait_type as '等待类型',
c.[host_name] as '主机名称',
c.[login_name] as '登录名',
c.[status] as '状态'
from sys.dm_tran_locks a
join sys.dm_os_waiting_tasks b on b.resource_address = a.lock_owner_address
join sys.dm_exec_sessions c on c.[session_id]=b.[session_id]
left join sys.partitions as p on a.resource_associated_entity_id=p.partition_id
--54指锁定资源的进程ID号,假设查出来的SPID为54,52
dbcc inputbuffer(54)
dbcc inputbuffer(52)