Get SQL Server deadlock graph instantly on mail when it occurs

Note: Best viewed in Internet Explorer 6 and above

Implementation: How to get SQL Server deadlock graph instantly on mail when deadlock occurs.

Scenario:Lets say,there is one site where you have been deployed and you face deadlock on SQL server mostly.Now you want to receive mail immediately with complete deadlock graph when SQL Server deadlock occurs.

Infrastructure: Below queries and processes are tested with SQL server2005,2008,2008 R2,2012 and so on..

Steps:.

First of all,build the queries to capture the deadlock details.once it is done then we will create the deadlock scenario for the testing purpose on test server.

Note:The below queries can be used in live server for capturing the deadlock details and to get SQL Server deadlock graph instantly on mail when it occurs.
Note: The deadlock graph is captured in sp_readerrorlog only when deadlock trace flags listed below must be enabled.

/* enable trace flags 1222,1204 at global level */

dbcc traceon(1204,-1)

dbcc traceon(1222,-1)

Or,

Go to startup parameter of SQL Server service by opening SQL Server configuration and enter the above two trace flag to get deadlock graph in sp_readerrorlog.This process needed restart of the DB service.
Below is the screen shot for the same.

Now once the deadlock trace flags get enabled do the following.

/* create the below table which will insert the content from sp_readerrorlog.

Please note this is one time table creation.You can create the table in any database except msdb,model and tempdb.

Create table CaptureErrorLogs

(

ID int identity(1,1),

LogDate DATETIME,

ProcessInfo VARCHAR(255),

Text VARCHAR(MAX)

)

/* insert everything from sp_readerrorlog to newly created table.Put the below whole code into job and do not scheduled it.This Job will automatically get executed when ever the deadlock occurs through the sql alerts.The creation of alerts will be showed latter.Now the job will trigger the mail only when the deadlock occurs which will be having current deadlock graph.

Created by: Dharmesh Mishra
Date/Time:23rd June 2015,09:00 PM
Like my fb page:www.facebook.com/sqlserverkillers
Visit my blogs:lapsql.wordpress.com

INSERT INTO CaptureErrorLogs

EXEC sp_readerrorlog

declare @subject varchar(250)
select @subject = ‘Deadlock reported on ‘ + @@servername
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DBA’, —change your DB profile name here
@recipients=’dharmesh@xyz.com’,
@subject =’Deadlock’,
@body = ‘A deadlock has been recorded. Further information can be found from the detailed message.’,
@body_format=’text’,
@attach_query_result_as_file = 1 ,
@query = ‘select logdate,processinfo,TEXT from CaptureErrorLogs
where id >=(select MAX(id) from CaptureErrorLogs where TEXT like ”%deadlock encountered%”) and
convert(varchar(10),LogDate,110) = convert(varchar(10),getdate()-0,110) ‘

truncate table CaptureErrorLogs

Kindly download the above query from below link.

https://app.box.com/s/9lvxkng2umb8lni6hju9eqscy611mw1r

Put this above script in a job and then create an alert which will execute the job when ever there is deadlock occurs.This job will send an attachment and you will receive mail with complete deadlock graph.
Note:

If you want the deadlock graph to be on mail with html format then use the below queries instead of above one and replace the below code in job to get html deadlock graph details.

INSERT INTO CaptureErrorLogs

EXEC sp_readerrorlog
DECLARE @tableHTML NVARCHAR(MAX) ;
declare @subject varchar(250)
select @subject = ‘Deadlock reported on ‘ + @@servername

SET @tableHTML =
N'<H1>Instant DeadLock Report </H1>’ +
N'<table border=”1″>’ +
N'<tr><th>LogDate</th><th>ProcessInfo</th>’ +

N'<th>Text</th></tr>’ +
CAST ( ( SELECT td = Logdate, ”,
td = processinfo, ”,
td = text, ”
from CaptureErrorLogs
where id >=(select MAX(id) from CaptureErrorLogs where TEXT like ‘%deadlock encountered%’) and
convert(varchar(10),LogDate,110) = convert(varchar(10),getdate()-0,110)

FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>’ ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DBA’,
@recipients=’dharmesh@xyz.com’,
@subject =@subject,
— @body = ‘A deadlock has been recorded. Further information can be found from the detailed message.’,

@body = @tableHTML,
@body_format = ‘HTML’ ;

truncate table CaptureErrorLogs

Kindly download the above query from below link.

https://app.box.com/s/6a9308ur7x2qxn2gmy9ghsvsumbvkni6

Now create an sql server agent alert.
Below is the screen shot for the same which is self explanatory

Above is the setting for the general page in alerts
Below is the setting for the response page

Below is setting for the option page

The last page is history page which shows the date and time the alerts triggers.
This is how following above steps you can receive mail with complete deadlock graph when sql server deadlock occurs.
So,its time to create to create the deadlock scenario

Note: Do not perform the below on Live server.This is only for your knowledge purpose.

Open a new session using ssms and fire the below code

create table deadlock1 (id int)
create table deadlock2 (id int)

insert deadlock1 values(1), (2), (3)

insert deadlock2 values(1), (2), (3)

begin transaction
update deadlock1 set id = 4 where id = 1

waitfor delay ’00:01:00′

update deadlock2 set id = 4 where id = 1
commit transaction

drop table deadlock1
drop table deadlock2

Now open the another query window and fire the f5 within 1 mints.

begin transaction
update deadlock2 set id = 4 where id = 1

waitfor delay ’00:00:20′

update deadlock1 set id = 4 where id = 1
commit transaction

when you do this then deadlock will occur.Below is the screen shot for the same.

So,here is the screen shot which you want after the above implementation.

This is how you can get SQL Server deadlock graph immediately on mail when it occurs.
Done….
Please do comments when you find this useful or any suggestion on the same will be highly accepted.