Email alert for deadlock report with .xml file attached

• Feivel

  SSCertifiable

  Points: 7524

  More actions

  November 30, 2017 at 8:52 am

  #330173

  Hello Folks,
  I am looking out for a T-SQL script that would send out an email alert as soon as it gets hold of the deadlock information and send out the .xml file as an attachment. I know this has been done in the past and I don't need to re-invent the wheel. However, I haven't found anything closest to what I am looking for 

  E.g alert would be
  Subject line would be "Deadlock occured on X server"
  Email body would just say- PFA.
  Attachment: .xml file
  I would appreciate if someone can provide me this information or at least give me a direction.

• Ant-Green

  SSC Guru

  Points: 113541

  More actions

  November 30, 2017 at 9:02 am

  #1970063

  
ALTER DATABASE InstanceMonitor SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE InstanceMonitor SET ENABLE_BROKER
GO
ALTER DATABASE InstanceMonitor SET MULTI_USER
GO
USE InstanceMonitor
GO
IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
BEGIN
    DROP TABLE DeadlockEvents ;
END ;
GO
CREATE TABLE DeadlockEvents
    (AlertTime DATETIME, DeadlockGraph XML) ;
GO
-- Add a job for the alert to run.
EXEC  msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph',
    @enabled=1,
    @description=N'Job for responding to DEADLOCK_GRAPH events' ;
GO
-- Add a jobstep that inserts the current time and the deadlock graph into
-- the DeadlockEvents table.
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Capture Deadlock Graph',
    @step_name=N'Insert graph into LogEvents',
    @step_id=1,
    @on_success_action=1,
    @on_fail_action=2,
    @subsystem=N'TSQL',
    @command= N'INSERT INTO DeadlockEvents
                (AlertTime, DeadlockGraph)
                VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData))))'')',
    @database_name=N'InstanceMonitor' ;
GO
-- Set the job server for the job to the current instance of SQL Server.
EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph' ;
GO
-- Add an alert that responds to all DEADLOCK_GRAPH events for
-- the default instance. To monitor deadlocks for a different instance,
-- change MSSQLSERVER to the name of the instance.
EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',
 @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
    @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
    @job_name='Capture Deadlock Graph' ;
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO


  This will be a starting point for you, will need a tweak to the job or alert to do a mail task with the inserted XML as an attachment