January 5, 2011 at 4:48 pm
I just tried that and also added the service account to the DatabaseMailUserProfile role in the msdb and that didnt make any difference either :crazy:
January 5, 2011 at 5:15 pm
Eureka! So I finally solved the issue - just by trail and error.
When I set the 'Run as user' to 'dbo' in the Job Step it runs without an error.
I can't explain why - but it works!
January 5, 2011 at 5:24 pm
Great.
Now let's hope you never actually get the email. 🙂
January 6, 2011 at 9:36 pm
It's triggered 3 times in the past 24 hours on our production server :sick:
February 10, 2011 at 11:40 am
I am getting two different messages from this procedure. If I run the job I get this error, " Incorrect syntax near '%'. [SQLSTATE 42000] (Error 102)."
If I run the select statement in a query window, I get this one, "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS".
Here is the query from the job that is flagging these errrors:
@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)',
I am running this on SQL2005.
Any ideas?
September 19, 2011 at 9:43 am
Geoff, I implemented this and I receive the e-mails but the attachments always contain no data other than columns headers (sample below). Any idea what I might be doing wrong?
Thanks, Steve
logdate procInfo ERRORLOG
----------------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(0 rows affected)
July 12, 2012 at 4:45 am
u need to add (deadlock-list) in ----> Like ''''%Deadlock encountered%''''
in the below query.
@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)'',
the correct query -
@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)'',
the reason behind this is the deadlock grap is not on in your production server .
September 21, 2012 at 4:02 am
Will performance be affected by a significant amount by utilising this job? Thanks
September 21, 2012 at 6:33 am
Only one thin Alert notifier=> response =>Excute job which job is selected...
September 21, 2012 at 7:01 am
Nice post geoff. Does anyone think using DBmail hence getting your sql server to send mails, is a security risk?
At my current place we seems to have .net utility that just sends mail with some parameters where you define osql or sqlcmd commands with the query you use to do any checks.. we seem to use batch files that include all this via scheduled tasks..
we have of course 2005 farm.
What do people think of this approach?
September 21, 2012 at 7:10 am
Don't forget that in SQL 2005 +, that the default trace (which starts automatically) captures deadlock graph information. So you can set up a job that fires when this event is raised, and just query that trace file to get the deadlock graph.
If you need help viewing the deadlock graph or figuring out what is going on, I have some code in the "Code Library" section of my blog for reading the deadlock graph and presenting the information in tabular form. It can be found here[/url]. This code shows how to get the deadlock graph from the default trace - you could use this in the routine that sends you the notification email.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 22, 2012 at 8:22 am
I made it about two years ago with i found it in msdn your method is very nice too.
September 23, 2012 at 11:15 pm
Nice article Geoff, I would like to request can u please help us to implement this type of deadlock alert notification proactively rather than re-actively.
September 24, 2012 at 9:30 am
abhijitshedulkar (9/23/2012)
Nice article Geoff, I would like to request can u please help us to implement this type of deadlock alert notification proactively rather than re-actively.
How would you get a deadlock alert notification proactively? In order for it to be proactive you'd have to get the alert BEFORE the deadlock occurs. I don't think that is possible. An expert could probably come in and do a code review and point to areas that could be prone to deadlocks.
Jack Corbett
Consultant - Straight Path Solutions
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
September 24, 2012 at 9:43 am
This is great and helpful, however, we had to go a little further. We have a severe load of code production deployments as our DBA responsibilities, so we are not always available to deal with Head Blocks right away, but just as soon as possible. Also, Head BLocks occurred as normal part of different processes and they last for a matter of a few seconds, so we do not need to know about all head blocks, but the long lasting ones. So for coping both needs, to be able to check later on and to report only the long lasting ones, we created a process that logs the Activity monitor entries (all columns) for head blocks to a table, and specific code process email alerts for only head blocks longer that 5 minutes. Furthermore, saving historical has increased our Knowledge of what goes on in many other counters, which opens a wide range of possibilities for activity research, analysis, and subsequent corrective actions.
Viewing 15 posts - 31 through 45 (of 69 total)
You must be logged in to reply to this topic. Login to reply