[SQL]死結處理語句

標籤：style   blog   http   color   os   io   資料   ar   

原文:[SQL]死結處理語句

引言

今天在群裡看到分享的解決死結的sql語句，就想著這東西以後肯定用的著，就下載下來，在這裡記錄一下，以後尋找也方便。

SQL

 1 SET QUOTED_IDENTIFIER ON 2 GO 3 SET ANSI_NULLS ON 4 GO 5 CREATE PROCEDURE sp_who_lock 6 AS 7     BEGIN 8         DECLARE @spid INT , 9             @bl INT ,10             @intTransactionCountOnEntry INT ,11             @intRowcount INT ,12             @intCountProperties INT ,13             @intCounter INT14         CREATE TABLE #tmp_lock_who15             (16               id INT IDENTITY(1, 1) ,17               spid SMALLINT ,18               bl SMALLINT19             )20          IF @@ERROR <> 021             RETURN @@ERROR22         INSERT INTO #tmp_lock_who ( spid, bl )23                 SELECT 0, blocked24                     FROM ( SELECT *25                             FROM sys.sysprocesses26                             WHERE blocked > 027                          ) a28                     WHERE NOT EXISTS ( SELECT *29                                         FROM ( SELECT *30                                                 FROM sys.sysprocesses31                                                 WHERE blocked > 032                                              ) b33                                         WHERE a.blocked = spid )34                 UNION35                 SELECT spid, blocked36                     FROM sys.sysprocesses37                     WHERE blocked > 038         IF @@ERROR <> 039             RETURN @@ERROR40        -- 找到暫存資料表的記錄數41         SELECT @intCountProperties = COUNT(*), @intCounter = 142             FROM #tmp_lock_who43          IF @@ERROR <> 044             RETURN @@ERROR45         IF @intCountProperties = 046             SELECT N‘現在沒有阻塞和死結資訊‘ AS message47        -- 迴圈開始48         WHILE @intCounter <= @intCountProperties49             BEGIN50               -- 取第一條記錄51                 SELECT @spid = spid, @bl = bl52                     FROM #tmp_lock_who53                     WHERE Id = @intCounter54                 BEGIN55                     IF @spid = 056                         SELECT N‘引起資料庫死結的是: ‘ + CAST(@bl AS VARCHAR(10))57                                 + N‘進程號,其執行的SQL文法如下‘58                     ELSE59                         SELECT N‘進程號SPID：‘ + CAST(@spid AS VARCHAR(10))60                                 + N‘被進程號SPID：‘ + CAST(@bl AS VARCHAR(10)) N‘阻塞,其當前進程執行的SQL文法如下‘61                     DBCC INPUTBUFFER (@bl )62                 END63               -- 迴圈指標下移64                 SET @intCounter = @intCounter + 165             END66         DROP TABLE #tmp_lock_who67         RETURN 068     END69 go70 EXEC sp_who_lock71 DROP PROC  sp_who_lock72 GO73 SET QUOTED_IDENTIFIER OFF74 GO75 76 SET ANSI_NULLS ON77 78 GO