Automated Deadlock Alerting

Your rating: None Average: 5 (1 vote)

Ever use the BOL code to capture Deadlock events as WMI events, but were left with how to effectively use? Well, same here! What I created is a script that utilizes my DBOPS database that I have on every server to log the information, but I also email the captured data via my custom email proc (sp_db_sendmail works just fine if you have all the accounts created properly). You also don't need a trace flag to make this work. I hope you like this.

Just as the email says, you can save the output to .xdl file and reopen with SSMS to see the graphical deadlock without the need for profiler!

I also found some nice code that shreads the XML and modified it to work for our data, see end of post for the query.

 

USE DBOPS
GO

IF OBJECT_ID('dbo.DeadlockEvents', 'U') IS NULL
BEGIN
CREATE TABLE
[dbo].[DeadlockEvents](
  
[AlertTime] [datetime] NULL PRIMARY KEY,
  
[DeadlockGraph] [xml] NULL
)
ON [PRIMARY]
END

USE [msdb]
GO

-- Allow SQL Agent to replace WMI tokens:
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO

IF NOT EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DBA: Capture Deadlock Graph')
BEGIN
BEGIN TRANSACTION
DECLARE
@ReturnCode INT, @ServerName VARCHAR(100)
SELECT @ReturnCode = 0, @ServerName = @@Servername

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA Monitoring' AND category_class=1)
BEGIN
EXEC
@ReturnCode = msdb.dbo.sp_add_category @class=N'JOB',  @name=N'DBA Monitoring'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA: Capture Deadlock Graph',
      
@enabled=1,
      
@notify_level_eventlog=0,
      
@notify_level_email=2,
      
@notify_level_netsend=0,
      
@notify_level_page=0,
      
@delete_level=0,
      
@description=N'Job for monitoring deadlock events',
      
@category_name=N'DBA Monitoring',
      
@owner_login_name=N'sa',
      
@notify_email_operator_name=N'ITDBOpsAlerts', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into LogEvents',
      
@step_id=1,
      
@cmdexec_success_code=0,
      
@on_success_action=3,
      
@on_success_step_id=0,
      
@on_fail_action=3,
      
@on_fail_step_id=0,
      
@retry_attempts=0,
      
@retry_interval=0,
      
@os_run_priority=0, @subsystem=N'TSQL',
      
@command=N'INSERT INTO DeadlockEvents
(AlertTime, DeadlockGraph)
VALUES (getdate(), Cast(replace(replace(Cast(N''$(ESCAPE_SQUOTE(WMI(TextData))))'' as nvarchar(max)),''</TextData>)'',''''),''<TextData>'','''') as XML))'
,
      
@database_name=N'DBOPS',
      
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'send email',
      
@step_id=2,
      
@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'DECLARE @tableHTML  NVARCHAR(MAX)
,@subject varchar(100)
,@Address varchar(300)  
select top 1 @tableHTML= convert(nvarchar(max),DeadlockGraph),@subject=''DeadLock Occured on '' + @@ServerName
from DeadlockEvents(nolock)
order by AlertTime desc

Select @Address = ISNULL(ParameterValue,''ITDBOps-Alerts@yourcompany.com)
from ProcessParameter where ParameterName = ''DBOPS & Dev Team Escalation''

Set @tableHTML = ''--Note: Save this output as .xdl file and open in SSMS to view graphically and remove this line.
'' + @tableHTML

select @tableHTML = replace (replace (@tableHTML,''&#x0A;'',''''),''&#x20;'','''')

exec dbops.dbo.prc_internalsendmail @HighPriority =1,
@address = @Address,
@subject=@subject,
@body=@tableHTML
',
      
@database_name=N'DBOPS',
      
@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 = @ServerName
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO
EndSave
QuitWithRollback:
    
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

END

GO

BEGIN Try
EXEC msdb.dbo.sp_help_alert @alert_name = 'Respond to DEADLOCK_GRAPH'
END Try
BEGIN Catch
  
IF @@ERROR = 14262 --Alert does not exist
  
EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',
          
@enabled=1,
      
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
      
@wmi_query=N'SELECT * FROM DEADLLOCK_GRAPH',
      
@job_name='DBA: Capture Deadlock Graph' ;
END Catch
 

A better way to view the deadlock information is to shread the xml like so:

;WITH CTE AS
(
SELECT
CAST(REPLACE(REPLACE(CAST(DeadlockGraph AS VARCHAR(MAX)),'</TextData>)',''),'<TextData>','') AS XML) AS DeadlockGraph
  
FROM [DBOPS].[dbo].[DeadlockEvents]
WHERE AlertTime =  '2012-07-16 11:01:24.283'
)
SELECT
[DeadlockGraph],
[PagelockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
[DeadlockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
[Victim] = CASE WHEN Deadlock.Process.value('@id', 'varchar(50)') = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') THEN 1 ELSE 0 END,
[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
[LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
FROM CTE
CROSS APPLY CTE.[DeadlockGraph].nodes('/deadlock-list/deadlock/process-list/process') AS Deadlock(Process)