How to receive Deadlock information automatically via email.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Geoff Albin » How to receive Deadlock information...

56 posts, Page 6 of 6

«««2 3 4 5 6

How to receive Deadlock information automatically via email.

Rate Topic

Display Mode

Topic Options

Author

Message

ESAT ERKEC

Posted Tuesday, February 26, 2013 1:15 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:19 AM
Points: 217, Visits: 697

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

Post #1423918

steve.robinson 29117

Posted Tuesday, February 26, 2013 9:06 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 25, 2013 4:16 PM
Points: 2, Visits: 17

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.

Post #1424132

ESAT ERKEC

Posted Thursday, February 28, 2013 5:29 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:19 AM
Points: 217, Visits: 697

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)))'')'

Post #1424998

ESAT ERKEC

Posted Thursday, February 28, 2013 5:29 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:19 AM
Points: 217, Visits: 697

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

in this link

Post #1424999

mik1

Posted Monday, March 11, 2013 9:02 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 4:28 AM
Points: 1, Visits: 7

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.

Post #1429325

Del Lee

Posted Monday, March 11, 2013 9:30 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:35 AM
Points: 235, Visits: 329

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

Post #1429349

« Prev Topic | Next Topic »

56 posts, Page 6 of 6

«««2 3 4 5 6

Permissions