How to receive Deadlock information automatically via email.

Question

How to receive Deadlock information automatically via email.

Viewing 15 posts - 46 through 60 (of 69 total)

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

Del Lee SSCrazy Points: 2781 More actions · Answer 1

I am also loving this! Thanks for contributing!

http://www.sqlservercentral.com/Forums/Topic1351590-146-1.aspx

Recently, Grant Fritchey suggested that I stick with Trace Flag 1222 only (see this link). I tried that with your code and pretty much got a blank email - no useful information. So, I went with all three trace flags, and I got much more information in the email. Unfortunately, it still did not bring down the hostname information and clientapp. Sure, we can go to the errorlog to still get it, but I don't see a good way to get that out of the errorlog and into the email. Anyone else see a good way to do it?

Del Lee

Tony Trus Say Hey Kid Points: 684 More actions · Answer 2

Love the script but wanted to be able to deploy this quicker. The final step of creating an alert I simply switched to TSQL and it achieves the same result without having to use the wizard:

--THIS GETS RUN AFTER THE JOB IS CREATED AND WILL CREATE THE ALERT

USE [msdb]

GO

declare

@jobid uniqueidentifier

set @jobid = isnull((select job_id from msdb.dbo.sysjobs where name = 'Deadlock Job'),'00000000-0000-0000-0000-000000000000')

set @jobid = LTRIM(rtrim(@jobid))

IF @jobid = '00000000-0000-0000-0000-000000000000'

BEGIN

RAISERROR('The deadlock job could not be found. Aborting!', 16, 1)

RETURN

END

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'DeadlockAlert')

BEGIN

RAISERROR('The alert already exists. Aborting!',16,1)

RETURN

END

EXEC msdb.dbo.sp_add_alert @name=N'DeadlockAlert',

@message_id=0,

@severity=0,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=0,

@category_name=N'[Uncategorized]',

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

@job_id= @jobid

GO

Neha05 Default port Points: 1494 More actions · Answer 3

Neha05

Default port

Points: 1494

December 25, 2012 at 8:38 am

#1571434

Nice article.

Tony Trus Say Hey Kid Points: 684 More actions · Answer 4

This was working like a champ for about a month and then suddenly SQL would email me and it would always say 0 rows affected. Anybody else run across this?

anishkollam SSC Journeyman Points: 85 More actions · Answer 5

Hi ,

I am getting email when a deadlock happens but i am getting a blank attachment due to which i am not able to know the actual cause of the deadlock

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 6

You can write deadlock events in a table it gives you a xml

steve.robinson 29117 Grasshopper Points: 20 More actions · Answer 7

I was also getting a blank attachment for a long time. I forget exactly how I fixed it but I'm receiving the deadlock information now. I tried posting the code here but kept getting an error. If you e-mail me directly at steve.robinson@telos.com I can send you the code I'm using.

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 8

Look at the theese sample you can use theese sample to send e-mail graph of xml

only you have to write send e-mail

In here

@command= N'INSERT INTO DeadlockEvents

(AlertTime, DeadlockGraph)

VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')'

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 9

http://msdn.microsoft.com/en-us/library/ms186385.aspx

in this link

mik1 Newbie Points: 9 More actions · Answer 10

Good job Geoff..

I've received the file having the details of the deadlock(s)... which is not easy to read. Can you help undestanding me how can I manipulate this inforamtion in order to find out information like user id, session id, dbid, query/stored procedure, application through which this occurred.

Del Lee SSCrazy Points: 2781 More actions · Answer 11

Below is the output from the errorlog. I copy this to a txt file and search for loginname, spid, hostname and so forth to get the type of information you are looking for.

process id=process8dfd38 taskpriority=0 logused=364 waitresource=KEY: 13:72057594070499328 (fe0993f4251a) waittime=828 ownerId=1052394084 transactionname=user_transaction lasttranstarted=2013-02-28T21:51:51.983 XDES=0x4ca947d8 lockMode=X schedulerid=3 kpid=15148 status=suspended spid=82 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-02-28T21:53:44.787 lastbatchcompleted=2013-02-28T21:51:51.983 hostpid=1844 loginname=BENEFITCOMPANY\vmadmin isolationlevel=read committed (2) xactid=1052394084 currentdb=13 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056

In a separate message you will find the SQL, which is pretty easy to pick out.

Del Lee

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

I missed this article the first time around. Nice job, Geoff.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

qbrt SSCrazy Points: 2737 More actions · Answer 13

Very nice, Geoff.

Here's a script that generates a deadlock to force the alert. Good to test the job.

Shamelessly lifted from some other forum, and since it worked for me, I am passing on the love.

--First run this code to create the tables and populate with data.

CREATE TABLE ##temp1 (Col1 INT)

CREATE TABLE ##temp2 (Col1 INT)

INSERT ##TEMP1

SELECT 1 UNION SELECT 2 UNION SELECT 3

INSERT ##TEMP2

SELECT 1 UNION SELECT 2 UNION SELECT 3

--Execute this in SSMS window #1

BEGIN TRAN

UPDATE ##temp1 SET Col1 = 4 WHERE Col1 = 3

--Delay long enough to lock ##temp1 in this process

--and allow ##temp2 to be locked in other process

WAITFOR DELAY '0:0:10'

--This is holding lock on ##temp1 waiting for ##temp2 to be released

UPDATE ##temp2 SET Col1 = 4 WHERE Col1 = 3

COMMIT TRAN

--Paste this code in another SSMS window and execute it

--SSMS window #2

BEGIN TRAN

UPDATE ##temp2 SET Col1 = 4 WHERE Col1 = 3

--Delay long enough to lock ##temp2 in this process

--and allow ##temp1 to be locked in other process

WAITFOR DELAY '0:0:10'

--This is holding lock on ##temp2 waiting for ##temp1 to be released

UPDATE ##temp1 SET Col1 = 4 WHERE Col1 = 3

COMMIT TRAN

/*

SELECT * FROM ##temp1

SELECT * FROM ##temp2

SELECT @@trancount

DROP TABLE ##temp1

DROP TABLE ##temp2

*/

Cheers

mocana SSC Rookie Points: 27 More actions · Answer 14

mocana

SSC Rookie

Points: 27

January 3, 2014 at 1:35 pm

#1677429

Hi, thanks, I will try this

sqldba.today SSC Veteran Points: 235 More actions · Answer 15

Well written article. Have you looked at using Event Notifications since I believe the default trace was deprecated since the release of SQL 2012? Event Notifications can capture the Deadlock graph.

Jonathan Kehayias has a great article on Event Notifications, Extended Events, and even the default trace. http://www.sqlskills.com/blogs/jonathan/event-notifications-vs-extended-events/