查看死锁代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】查看死锁

作者:Dezai.CN / 发布于2013/3/26/ 671


	use master
	go
	create procedure sp_who_lock
	as
	begin
	declare @spid int,@bl int,
	@intTransactionCountOnEntry int,
	 @intRowcount int,
	 @intCountProperties int,
	 @intCounter int
	create table #tmp_lock_who (
	id int identity(1,1),
	spid smallint,
	bl smallint)

	IF @@ERROR<>0 RETURN @@ERROR

	insert into #tmp_lock_who(spid,bl) select 0 ,blocked
	 from (select * from sysprocesses where blocked>0 ) a
	 where not exists(select * from (select * from sysprocesses where blocked>0 ) b
	 where a.blocked=spid)
	 union select spid,blocked from sysprocesses where blocked>0

	IF @@ERROR<>0 RETURN @@ERROR
	
	-- 找到临时表的记录数
	select @intCountProperties = Count(*),@intCounter = 1
	from #tmp_lock_who

	IF @@ERROR<>0 RETURN @@ERROR

	if @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_who

	return 0
	end

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　死锁　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)