常用脚本--查看死锁和阻塞usp_who_lock
USE [master] GO /****** Object: StoredProcedure [dbo].[sp_who_lock] Script Date: 02/07/2014 11:51:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_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 ) IF @spid>0 DBCC INPUTBUFFER (@spid ) END -- 循环指针下移 SET @intCounter = @intCounter + 1 END DROP TABLE #tmp_lock_who RETURN 0 END GO --=============================== --Usage EXEC [master].[dbo].[usp_who_lock]