Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

How to receive Deadlock information automatically via email. Expand / Collapse
Author
Message
Posted Monday, December 13, 2010 8:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:12 AM
Points: 10,910, Visits: 12,553
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
Posted Monday, December 13, 2010 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 3:46 PM
Points: 8, Visits: 28

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
Posted Monday, December 13, 2010 12:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, December 13, 2010 3:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 20,484, Visits: 14,138
Thanks for the nice resource.



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


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
Posted Monday, December 13, 2010 3:35 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 378, Visits: 845
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
Posted Tuesday, December 14, 2010 8:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, December 14, 2010 8:33 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:29 AM
Points: 510, Visits: 1,685
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
Posted Tuesday, December 14, 2010 1:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 10, 2013 11:20 AM
Points: 36, Visits: 733
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
Posted Tuesday, December 14, 2010 1:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 1:41 PM
Points: 50, Visits: 70
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
Posted Thursday, December 16, 2010 2:33 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 11:29 AM
Points: 510, Visits: 1,685
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 »

Add to briefcase ««12345»»»

Permissions Expand / Collapse