查询Sqlserver数据库死锁的一个存储过程

/************************************************************
?* Code formatted by SoftTree SQL Assistant ?v6.1.35
?* Time: 2013/11/12 16:48:00
?************************************************************/

IF OBJECT_ID('proc_who_lock') IS NOT NULL BEGIN ??? DROP PROC proc_who_lock END GO CREATE PROCEDURE proc_who_lock AS BEGIN ?DECLARE @spid INT???? ?DECLARE @blk INT???? ?DECLARE @count INT???? ?DECLARE @index INT???? ?DECLARE @lock TINYINT????? ?SET @lock = 0????? ?CREATE TABLE #temp_who_lock ?( ??id?????? INT IDENTITY(1,1),??spid???? INT,??blk????? INT ?)????? ?IF @@error <> 0 ???? RETURN @@error ? ?INSERT INTO #temp_who_lock ?? ( ???? spid,???? blk ?? ) ?SELECT 0,??????? blocked ?FROM?? ( ??????????? SELECT * ??????????? FROM?? MASTER..sysprocesses ??????????? WHERE? blocked > 0 ??????? )a ?WHERE? NOT EXISTS( ??????????? SELECT * ??????????? FROM?? MASTER..sysprocesses ??????????? WHERE? a.blocked = spid ?????????????????? AND blocked > 0 ??????? ) ?UNION ?SELECT spid,??????? blocked ?FROM?? MASTER..sysprocesses ?WHERE? blocked > 0 ? ?IF @@error <> 0 ???? RETURN @@error ? ?SELECT @count = COUNT(*),??????? @index = 1 ?FROM?? #temp_who_lock ? ?IF @@error <> 0 ???? RETURN @@error ? ?IF @count = 0 ?BEGIN ???? SELECT '没有阻塞和死锁信息' ???? RETURN 0 ?END ? ?WHILE @index <= @count ?BEGIN ???? IF EXISTS( ??????????? SELECT 1 ??????????? FROM?? #temp_who_lock a ??????????? WHERE? id > @index ?????????????????? AND EXISTS( ?????????????????????????? SELECT 1 ?????????????????????????? FROM?? #temp_who_lock ?????????????????????????? WHERE? id <= @index ????????????????????????????????? AND a.blk = spid ?????????????????????? ) ??????? ) ???? BEGIN ???????? SET @lock = 1????? ???????? SELECT @spid = spid,??????????????? @blk = blk ???????? FROM?? #temp_who_lock ???????? WHERE? id = @index ???????? ???????? SELECT '引起数据库死锁的是: ' + CAST(@spid AS VARCHAR(10)) + ??????????????? '进程号,其执行的SQL语法如下' ???????? ???????? SELECT @spid,??????????????? @blk ???????? ???????? DBCC INPUTBUFFER(@spid) ???????? DBCC INPUTBUFFER(@blk) ???? END ???? ???? SET @index = @index + 1 ?END???? ?IF @lock = 0 ?BEGIN ???? SET @index = 1????? ???? WHILE @index <= @count ???? BEGIN ???????? SELECT @spid = spid,??????????????? @blk = blk ???????? FROM?? #temp_who_lock ???????? WHERE? id = @index ???????? ???????? IF @spid = 0 ???????????? SELECT '引起阻塞的是:' + CAST(@blk AS VARCHAR(10)) + ??????????????????? '进程号,其执行的SQL语法如下' ???????? ELSE ???????????? SELECT '进程号SPID：' + CAST(@spid AS VARCHAR(10)) + '被' + ??????????????????? '进程号SPID：' + CAST(@blk AS VARCHAR(10)) + ??????????????????? '阻塞,其当前进程执行的SQL语法如下' ???????? ???????? DBCC INPUTBUFFER(@spid) ???????? DBCC INPUTBUFFER(@blk)????? ???????? SET @index = @index + 1 ???? END ?END ? ?DROP TABLE #temp_who_lock ?RETURN 0 END GO ? EXEC proc_who_lock

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!