Monitor/Alert deadlock events

Question

Post reply

Monitor/Alert deadlock events

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
May 24, 2016 at 8:24 am

#327258

Which is the best way to alert/email deadlock events/graph in sql server 2014(24/7 monitoring). I would like to use a lightweight option without causing resource issues.
Enable logging to error log by enabling messages 1205/3928 and setup alerts for these errors?
Enable trace flags 1204/1222?
Use Profiler trace?
Use Extended events?
Many thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

gfoxxy93 Right there with Babe Points: 776 More actions · Answer 1

I would personally go with extended events. (Bit of an explanation here)

Lowell SSC Guru Points: 323504 More actions · Answer 2

well here's how i do it:

deadlocks are already in the extended events. nothing extra needs to be done with that.

i have a stored procedure that pulls the "latest" deadlock from the extended events, does some xml shredding for an email body, and emails me the deadlock *.xdl as an attachment.

Jonathan Kehayias' article on deadlocks is what i used for the base of my code.

http://www.sqlservercentral.com/Forums/Topic661084-1365-3.aspx

the procedure can be called from a SQL job.

the job is called from a SQL agent alert when a deadlock actually occurs, so i get the deadlock graph in an email for later review, with only a slight delay from when it occurred.

If i got a lot of emails, i know something major is going on.;

/****** Object: Alert [Deadlock Alert] Script Date: 5/24/2016 11:27:23 AM ******/

EXEC msdb.dbo.sp_add_alert @name=N'Deadlock Alert',

@message_id=0,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=0,

@category_name=N'[Uncategorized]',

@performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0',

@job_id=N'af2d5ee6-e317-46b6-bd47-c3a194b53058' --Deadlock Job

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 3

Just adding a vote for Lowell's solution. Extended Events are absolutely the right way to take care of deadlocks and deadlock monitoring. If you do the research, you're going to see lots of suggestions for setting traceflags on the server. That is a very old school approach. Don't follow it. Stick with what Lowell has outlined.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning