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 2 of 6

««1 2 3 4 5 »»»

How to receive Deadlock information automatically via email.

Rate Topic

Display Mode

Topic Options

Author

Message

Jack Corbett

Posted Monday, December 13, 2010 8:21 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

Nice post, especially since it works from 200 up.

With 2005 and later you should look into Event Notifications or with 2008 and later Extended Events. These can make this type of monitoring simpler once you understand how they work.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #1033840

massoud-1045248

Posted Monday, December 13, 2010 9:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 7:59 AM
Points: 7, Visits: 25

Thanks for the article.After reboot, I tried going through the steps but when I set up the alert at the end, I get the following error message:
The specified object_name('SQLServer:Loks') does not exist. (Microsoft SQL Server, Error: 14262)

Post #1033925

alxdean

Posted Monday, December 13, 2010 12:05 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 11:35 PM
Points: 2, Visits: 7

Hey Geoff. Thanks for pointing that out. I just followed up the limitations due to the query happening on a different session. Have you tried it with Global Temporary tables using the double hash syntax? ##tablename?

regards,

Alex

Post #1034014

SQLRNNR

Posted Monday, December 13, 2010 3:24 PM

SSCoach

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732, Visits: 12,329

Thanks for the nice resource.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1034107

JayK

Posted Monday, December 13, 2010 3:35 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:49 PM
Points: 366, Visits: 689

When I run the sql contained in the job within the QA it works Ok and I get a mail. However when I try and execute the job itself I get the following error:

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

SQL with email just masked:

CREATE TABLE tempdb.dbo.ErrorLog (Id int IDENTITY (1, 1) NOT NULL,
logdate DATETIME, procInfo VARCHAR(10), ERRORLOG VARCHAR(MAX))
--== We create a 3 column table to hold the contents of the SQL Server Error log. ==--
--== Then we insert the actual data from the Error log into our newly created table. ==--
INSERT INTO tempdb.dbo.ErrorLog
EXEC master.dbo.sp_readerrorlog
--== With our table created and populated, we can now use the info inside of it. ==--
BEGIN
--== Set a variable to get our instance name. ==--
--== We do this so the email we receive makes more sense. ==--
declare @servername nvarchar(150)
set @servername = @@servername
--== We set another variable to create a subject line for the email. ==--
declare @mysubject nvarchar(200)
set @mysubject = 'Deadlock event notification on server '+@servername+'.'
--== Now we will prepare and send the email. Change the email address to suite your environment. ==--
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients='xxxxxxxxxxxxxxxxxx',
@subject = @mysubject,
@body = 'Deadlock has occurred. View attachment to see the deadlock info',
@query = '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)',
@query_result_width = 600,
@attach_query_result_as_file = 1
END

--== Clean up our process by dropping our temporary table. ==--
DROP TABLE tempdb.dbo.ErrorLog

Any ideas? I'm running 2008 R2 but not sure if that should make a difference.Thanks!

Post #1034111

chuckh 3191

Posted Tuesday, December 14, 2010 8:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 08, 2011 2:33 PM
Points: 2, Visits: 9

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

Post #1034471

Geoff A

Posted Tuesday, December 14, 2010 8:33 AM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:01 AM
Points: 485, Visits: 1,566

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.

Post #1034474

Arman Khan

Posted Tuesday, December 14, 2010 1:02 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 19, 2013 9:21 AM
Points: 36, Visits: 727

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)

Post #1034675

shotman

Posted Tuesday, December 14, 2010 1:53 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, March 28, 2013 11:51 AM
Points: 50, Visits: 62

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.

Post #1034720

Geoff A

Posted Thursday, December 16, 2010 2:33 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:01 AM
Points: 485, Visits: 1,566

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?

Post #1036160

« Prev Topic | Next Topic »

56 posts, Page 2 of 6

««1 2 3 4 5 »»»

Permissions