如何自动检测死锁并发送警告邮件

最新推荐文章于 2024-06-27 09:49:05 发布

Mr_Nice

最新推荐文章于 2024-06-27 09:49:05 发布

阅读量1k

点赞数

分类专栏： # DBA 文章标签： sql server 邮件死锁

DBA 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

并发事务处理中发生死锁是我们作为DBA不想看到的，但又无法避免的常见实际问题。能否数据库自动检测发生死锁，并将此信息发送到管理员邮箱呢？

Look here ！

首先，我们要捕捉到死锁状态。在SQL Server 中，捕捉状态最常用的方法是跟踪（Trace) ，具体要跟踪的项目在sql server 中叫做跟踪标记 (trace Flags) .

跟踪死锁，我们就用到了 1204 这个标记。

DBCC TRACEON (3605,1204,1222,-1)

--1204 就是跟踪死锁的标记了

--1222 在官网上写的实在是让人费解。其实就是死锁更详细的信息，比如锁定资源，类型等。
注意，在SQL 2K5 以上才使用1222，在SQL 2K 中，详细信息要使用1205标记。

--3605 这个标记很多地方使用到，但最新的msdn所提供的信息里，却少之又少。
追溯到更久远的历史，SQL Server 6.5（对没错，就是6.5）中，就已经开始使用了。如何捕获从 SQL Server 的实例发送到客户端的错误消息

-- -1 最后要说的是这个-1。如果使用中要做到在全局作用域内启用跟踪标志，就需要添加这个-1参数。如果仅跟踪当前会话的话，就不使用-1。

这一步做完了，数据库本身就已经开始跟踪死锁了。接下来，需要确认数据库可以发送邮件。走起：如何配制数据库发送邮件

做完上面的步骤，就剩下最后一步了。如何在获取死锁信息的时候，发送邮件。（差点儿忘记说了，建立跟踪的信息，都存在了log中）

--读取Log信息（SQL 2K5及以上）
--创建临时表，用于已数据列形式存储log信息
 IF OBJECT_ID('tempdb.dbo.Log_Error') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.Log_Error
 END


CREATE TABLE tempdb.dbo.Log_Error
    (
      id INT IDENTITY(1, 1)
             NOT NULL ,
      记录时间 DATETIME ,
      进程信息 VARCHAR(10) ,
      ERROR_LOG VARCHAR(MAX)
    )


 INSERT INTO tempdb.dbo.Log_Error
 EXEC master.dbo.sp_readerrorlog     --这里还有另外一个systemproc叫做：xp_readerrorlog 也可以用来查看log信息，有兴趣的同学可以google一下


 BEGIN	
	DECLARE @servername nvarchar(150)
	SET @servername = @@servername


	declare @subject nvarchar(200)
	set @subject = '由服务器 '+@servername+' 发出的死锁提醒.'


 EXEC msdb.dbo.sp_send_dbmail @recipients='OrchidCat@gmail.com',    --之前提到的发送数据库邮件
 @subject = @subject,
 @body = '死锁已发生，请查看附件信息',
 @query = 'select 记录时间, 进程信息, ERRORLOG 
			from tempdb.dbo.Log_Error 
			where Id >= (select TOP 1 Id 
						from tempdb.dbo.Log_Error 
						WHERE ERROR_LOG Like ''%Deadlock encountered%'' 
						order by Id DESC)',
 @query_result_width = 600,
 @attach_query_result_as_file = 1
 END


 DROP TABLE tempdb.dbo.Log_Error

上面的代码，可将获取的error信息，以邮件方式发送到指定邮箱。最后要处理的就是在死锁发生时，执行上面的代码。

SQL 在这里设定一个叫做作业的东东（Job）

作业——（右键）管理作业类别——添加（添加一个：SQL Server 性能警报）