SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to receive Deadlock information automatically via email.


How to receive Deadlock information automatically via email.

Author
Message
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42101 Visits: 14925
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
massoud-1045248
massoud-1045248
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 46
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)
alxdean
alxdean
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63625 Visits: 18570
Thanks for the nice resource.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

JayK
JayK
Right there with Babe
Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

Group: General Forum Members
Points: 793 Visits: 1133
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!
chuckh 3191
chuckh 3191
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 9
I'm trying to remember. Do you have to turn back on DBCC TRACEON each time you restart the instance?
Geoff A
Geoff A
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2344 Visits: 1808
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
Arman Khan
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 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)
shotman
shotman
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 92
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
Geoff A
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2344 Visits: 1808
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search