发生死锁时自动发mail

对于DBA，死锁有时是件很让人头痛的事。往往用户反映说系统卡死了，半天没反应，这时我们去查看活动监视器(Activity Monitor)，或通过T-SQL（可参考这里），或通过Trace等手段，发现数据库发生死锁了。当系统发生死锁时，有没有办法让系统就立即触发并发mail通知相关死锁信息呢？答案是有的。实现之前要确保两件事情：

• 开启死锁跟踪标志
• 数据库邮件已配置并且能正常工作（这部分可以参考我之前的Blog）

下面介绍一下如何开启死锁跟踪标志：

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

标志的含义如下：
3605：将DBCC的结果输出到错误日志
1204：返回参与死锁的锁的资源和类型，以及受影响的当前命令。
作用域：仅全局
1222：以不符合任何 XSD 架构的 XML 格式，返回参与死锁的锁的资源和类型，以及受影响的当前命令。
作用域：仅全局
更多关于TRACEON的信息可参考MSDN，跟踪标志可参考MSDN。

重要提示：这种设置跟踪标志的方式在SQL Server重启后将不再有效，如果要保证SQL Server运行时总是开启，你可以使用-T启动选项（可参考MSDN）或创建一个SQL Server代理作业，当代理启动时自动开启。关闭跟踪标志可执行：DBCC TRACEOFF(3605,1204,1222,-1) 

接下来，看看如何抓取死锁信息并发Mail，相关Script如下：

--创建临时表
 IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END

CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, 
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--向临时表插入错误日志信息
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--过滤错误日志信息并将死锁信息发出mail
 BEGIN
	 declare @servername nvarchar(150)= @@servername
	 declare @mysubject nvarchar(200) = 'Deadlock event notification on server '+@servername+'.'
	 --发mail
	 EXEC msdb.dbo.sp_send_dbmail 
	 @profile_name = 'Local DBA',
	 @recipients='Burgess.Liu@quantacn.com',
	 @subject = @mysubject,
	 @body = 'Deadlock has occurred. View attachment to see the deadlock info',
	 @query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)',
	 @query_result_width = 600,
	 @attach_query_result_as_file = 1 
 END
 --Drop临时表
 DROP TABLE tempdb.dbo.ErrorLog 

 

为了在发生死锁时就触发发出mail，我们可以使用SQL代理作业，这里只提供Script：

USE [msdb]
GO

/****** Object:  Job [Deadlock Job]    Script Date: 2012/5/28 12:05:21 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2012/5/28 12:05:21 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Deadlock Job', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Deadlock has occurred.]    Script Date: 2012/5/28 12:05:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Deadlock has occurred.', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'--创建临时表
 IF OBJECT_ID(''tempdb.dbo.ErrorLog'') IS Not Null
 BEGIN
 DROP TABLE tempdb.dbo.ErrorLog
 END

CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL, 
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--向临时表插入错误日志信息
 INSERT INTO tempdb.dbo.ErrorLog
 EXEC master.dbo.sp_readerrorlog
--过滤错误日志信息并将死锁信息发出mail
 BEGIN
 declare @servername nvarchar(150) = @@servername
 declare @mysubject nvarchar(200) = ''Deadlock event notification on server ''+@servername+''.''
--发mail
 EXEC msdb.dbo.sp_send_dbmail @recipients=''DBA_Super_Hero@email.com'',
 @subject = @mysubject,
 @body = ''Deadlock has occurred. View attachment to see the deadlock info'',
 @query = ''select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''''%Deadlock encountered%'''' order by Id DESC)'',
 @query_result_width = 600,
 @attach_query_result_as_file = 1
 END
 --== Clean up our process by dropping our temporary table. ==--
 DROP TABLE tempdb.dbo.ErrorLog
', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

最后，设置作业的警报(Alert)，可参考下图：

到此，任务完成，下次当数据库发生死锁时，你就可以收到相关的死锁信息，如下图：