在SQL2000、SQL2005查看阻塞进程的SQL脚本

最新推荐文章于 2019-10-25 16:30:56 发布

中国风

最新推荐文章于 2019-10-25 16:30:56 发布

阅读量4.5k

点赞数

文章标签： sql 脚本

本文链接：https://blog.csdn.net/roy_88/article/details/2699225

版权

--查阻塞
/******************************************************************************************************************************************************
整理人：中国风(Roy)

日期:
******************************************************************************************************************************************************/
--SQL2000

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

SQL2005:

with Lock(dbName,spid,blocked,sql_handle)
as
(
select 
    db_name(dbid),0,blocked,sql_handle
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,sql_handle
from
    master..sysprocesses a
where
    Blocked>0
)
select * from Lock a cross apply     sys.dm_exec_sql_text(a.sql_handle)b