Post reply

Email alert for deadlock report with .xml file attached

  • 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.

  • 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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply