How to receive Deadlock information automatically via email.

Question

How to receive Deadlock information automatically via email.

Viewing 15 posts - 16 through 30 (of 69 total)

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

chuckh 3191 SSC Rookie Points: 40 More actions · Answer 1

I'm trying to remember. Do you have to turn back on DBCC TRACEON each time you restart the instance?

Geoff A SSChampion Points: 11511 More actions · Answer 2

chuckh 3191 (12/14/2010)
I'm trying to remember. Do you have to turn back on DBCC TRACEON each time you restart the instance?

yes.

from the article -

It is important to note that setting trace flags this way will only work until the next time SQL Server is restarted. If you want to make sure your trace flags are always set this way, you can edit the startup options of SQL Server by using the -T switch or creating a SQL Agent job to run that code when the Agent starts.

Arman Khan SSC Eights! Points: 864 More actions · Answer 3

Great post,

But i have a problem that i am receving the emails with empty Attachment and also what is the Schedule for Sql Agent Job?

l

ogdate procInfo ERRORLOG

----------------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(0 rows affected)

shotman Right there with Babe Points: 769 More actions · Answer 4

Doesn't enabling deadlock graph take performance hit on the system.....true or not? As on our systems i have seen some performance hit when deadlock graph is enabled.

Geoff A SSChampion Points: 11511 More actions · Answer 5

yes, there is a very minor hit, but consider the alternative. everything comes at a cost. would you rather have unknown deadlocks or (IMHO) a barely measurable hit in performance?

gary1 SSC-Insane Points: 21152 More actions · Answer 6

reat post,
But i have a problem that i am receiving the emails with empty Attachment and also what is the Schedule for Sql Agent Job?
l

I followed the steps discussed in the article and everything works great but getting EMPTY ATTACHMENT in the email when deadlock occurs.!! I'm able to see the dead lock graph in the errorlog but not getting the email attachment.

please see the attachment

Geoff A SSChampion Points: 11511 More actions · Answer 7

what happens if you just run this code?

see attachment....

gary1 SSC-Insane Points: 21152 More actions · Answer 8

I ran the below code, but got NO results

IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
BEGIN
DROP TABLE tempdb.dbo.ErrorLog
END
CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
INSERT INTO tempdb.dbo.ErrorLog
EXEC master.dbo.sp_readerrorlog
select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like '%Deadlock encountered%' order by Id DESC)
DROP TABLE tempdb.dbo.ErrorLog

Results:

logdate procinfo ERRORLOG

Note: I do have the Deadlock graph in SQL Server errorlog. but the above query is not fetching that deadlock details.

Please advice

gary1 SSC-Insane Points: 21152 More actions · Answer 9

I got the results:

we just need to change the below in your query:

select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like '%deadlock-list%' order by Id DESC)

thanks

gary1 SSC-Insane Points: 21152 More actions · Answer 10

Geoff,

Do you have similar kind of script to notify when blocking occurs?

We are experiencing blocking and do not have script to notify. please advice..Thanks

Geoff A SSChampion Points: 11511 More actions · Answer 11

sure, but blocking will need to be a timed query Ie, via SQl Server Agent....

but you could run a script like in the attachment every so often and send the results, (if there are any) to an email address....

see attachment...

JayK SSCrazy Points: 2679 More actions · Answer 12

I still cant get this script to work with an SQL SERVER AGENT job - I am trying to solve it over on this forum post but still no luck...

http://www.sqlservercentral.com/Forums/FindPost1042873.aspx

did anyone else have the same issue?

Geoff A SSChampion Points: 11511 More actions · Answer 13

based on that other thread, and all the info that is there, can you try this;

DECLARE @sqlquery nvarchar(250)

SET @sqlquery = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)'

exec (@SQLQuery)

and tell me if that work on the server in question...

JayK SSCrazy Points: 2679 More actions · Answer 14

Hi Geoff,

Thanks for getting back. Yes that works without a problem. The issue is this statement:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'default',

@recipients = 'xxxxx@xxxxxxx',

@subject = @mysubject,

@body = 'Deadlock has occurred. View attachment to see the deadlock info.',

@query = @sqlquery,

@query_result_width = 600,

@attach_query_result_as_file = 1

The job falls over when it comes to execute the sp_send_dbmail command.

Geoff A SSChampion Points: 11511 More actions · Answer 15

Maybe you can try to;

Grant execute on sp_send_dbmail to <user>