demosql46.sql

create proc dba_ProcessDeadlockGraphs
as
set nocount on;
DECLARE @deadlock XML;
DECLARE @email_message nvarchar(MAX);

select top 1
    @deadlock=DeadlockGraph
from (
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
, XEvent.value('(/event/@timestamp)[1]','DATETIMEOFFSET') AS BeginTime
    FROM ( SELECT XEvent.query('.') AS XEvent
        FROM ( SELECT CAST(target_data AS XML) AS TargetData
            FROM sys.dm_xe_session_targets st
                JOIN sys.dm_xe_sessions s
                ON s.address = st.event_session_address
            WHERE s.name = 'system_health'
                AND st.target_name = 'ring_buffer' 
              ) AS Data 
              CROSS APPLY 
                 TargetData.nodes 
                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
              AS XEventData ( XEvent ) 
      ) AS src
) result
order by BeginTime desc

SELECT @email_message = CONVERT(nvarchar(max), @deadlock)
EXEC msdb.dbo.sp_send_dbmail
             @profile_name = 'ricogoogle', -- your defined email profile 
             @recipients = 'abc@gmail.com', -- your email
             @subject = 'Deadlock Notification',
             @body = @email_message;