InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 13:45:07 GMT20http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxBelow is the output from the errorlog. I copy this to a txt file and search for loginname, spid, hostname and so forth to get the type of information you are looking for.process id=process8dfd38 taskpriority=0 logused=364 waitresource=KEY: 13:72057594070499328 (fe0993f4251a) waittime=828 ownerId=1052394084 transactionname=user_transaction lasttranstarted=2013-02-28T21:51:51.983 XDES=0x4ca947d8 lockMode=X schedulerid=3 kpid=15148 status=suspended spid=82 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-02-28T21:53:44.787 lastbatchcompleted=2013-02-28T21:51:51.983 hostpid=1844 loginname=BENEFITCOMPANY\vmadmin isolationlevel=read committed (2) xactid=1052394084 currentdb=13 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056In a separate message you will find the SQL, which is pretty easy to pick out.Mon, 11 Mar 2013 09:30:17 GMTDel Leehttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxGood job Geoff.. I've received the file having the details of the deadlock(s)... which is not easy to read. Can you help undestanding me how can I manipulate this inforamtion in order to find out information like user id, session id, dbid, query/stored procedure, application through which this occurred.Mon, 11 Mar 2013 09:02:20 GMTmik1http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxhttp://msdn.microsoft.com/en-us/library/ms186385.aspx in this linkThu, 28 Feb 2013 05:29:35 GMTESAT ERKEChttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxLook at the theese sample you can use theese sample to send e-mail graph of xml only you have to write send e-mailIn here[u][b]@command= N'INSERT INTO DeadlockEvents (AlertTime, DeadlockGraph) VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')'[/b][/u]Thu, 28 Feb 2013 05:29:06 GMTESAT ERKEChttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxI was also getting a blank attachment for a long time. I forget exactly how I fixed it but I'm receiving the deadlock information now. I tried posting the code here but kept getting an error. If you e-mail me directly at steve.robinson@telos.com I can send you the code I'm using.Tue, 26 Feb 2013 09:06:55 GMTsteve.robinson 29117http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspx You can write deadlock events in a table it gives you a xmlTue, 26 Feb 2013 01:15:02 GMTESAT ERKEChttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxHi ,I am getting email when a deadlock happens but i am getting a blank attachment due to which i am not able to know the actual cause of the deadlockTue, 26 Feb 2013 00:57:08 GMTanishkollamhttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxThis was working like a champ for about a month and then suddenly SQL would email me and it would always say 0 rows affected. Anybody else run across this?Tue, 25 Dec 2012 10:28:52 GMTTony Trushttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxNice article.Tue, 25 Dec 2012 08:38:38 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxLove the script but wanted to be able to deploy this quicker. The final step of creating an alert I simply switched to TSQL and it achieves the same result without having to use the wizard:--THIS GETS RUN AFTER THE JOB IS CREATED AND WILL CREATE THE ALERTUSE [msdb]GOdeclare @jobid uniqueidentifierset @jobid = isnull((select job_id from msdb.dbo.sysjobs where name = 'Deadlock Job'),'00000000-0000-0000-0000-000000000000')set @jobid = LTRIM(rtrim(@jobid))IF @jobid = '00000000-0000-0000-0000-000000000000' BEGIN RAISERROR('The deadlock job could not be found. Aborting!', 16, 1) RETURN ENDIF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'DeadlockAlert') BEGIN RAISERROR('The alert already exists. Aborting!',16,1) RETURN ENDEXEC msdb.dbo.sp_add_alert @name=N'DeadlockAlert', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=60, @include_event_description_in=0, @category_name=N'[Uncategorized]', @performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0', @job_id= @jobidGOWed, 26 Sep 2012 09:12:38 GMTTony Trushttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxI am also loving this! Thanks for contributing! http://www.sqlservercentral.com/Forums/Topic1351590-146-1.aspxRecently, Grant Fritchey suggested that I stick with Trace Flag 1222 only (see this [url=http://www.sqlservercentral.com/Forums/Topic1351590-146-1.aspx]link[/url]). I tried that with your code and pretty much got a blank email - no useful information. So, I went with all three trace flags, and I got much more information in the email. Unfortunately, it still did not bring down the hostname information and clientapp. Sure, we can go to the errorlog to still get it, but I don't see a good way to get that out of the errorlog and into the email. Anyone else see a good way to do it?Mon, 24 Sep 2012 14:05:33 GMTDel Leehttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxThis 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.Mon, 24 Sep 2012 09:43:07 GMTjraez 82559http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspx[quote][b]abhijitshedulkar (9/23/2012)[/b][hr]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.[/quote]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.Mon, 24 Sep 2012 09:30:28 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxNice 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.Sun, 23 Sep 2012 23:15:48 GMTabhijitshedulkarhttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxI made it about two years ago with ı found it in msdn your method is very nice too.http://msdn.microsoft.com/en-us/library/ms186385.aspxSat, 22 Sep 2012 08:22:34 GMTESAT ERKEChttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxDon'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 [url=http://blog.waynesheffield.com/wayne/code-library/shred-deadlock-graph/][u]here[/u][/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.Fri, 21 Sep 2012 07:10:28 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxNice 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?Fri, 21 Sep 2012 07:01:53 GMThp_dba_ukhttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxOnly one thin Alert notifier=> response =>Excute job which job is selected...Fri, 21 Sep 2012 06:33:08 GMTdineshvishehttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxWill performance be affected by a significant amount by utilising this job? ThanksFri, 21 Sep 2012 04:02:11 GMTAIRWALKER-375999http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxu 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 .Thu, 12 Jul 2012 04:45:05 GMTIvan Mohapatrahttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxGeoff, 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, Stevelogdate procInfo ERRORLOG ----------------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------(0 rows affected)Mon, 19 Sep 2011 09:43:36 GMTsteve.robinson 29117http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxI 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?Thu, 10 Feb 2011 11:40:56 GMTDBA_Oshvegashttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxIt's triggered 3 times in the past 24 hours on our production server :sick:Thu, 06 Jan 2011 21:36:30 GMTJayKhttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxGreat.Now let's hope you never actually get the email. :-)Wed, 05 Jan 2011 17:24:07 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxEureka! 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!Wed, 05 Jan 2011 17:15:51 GMTJayKhttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxI just tried that and also added the service account to the DatabaseMailUserProfile role in the msdb and that didnt make any difference either :crazy:Wed, 05 Jan 2011 16:48:33 GMTJayKhttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxMaybe you can try to;Grant execute on sp_send_dbmail to <user>Wed, 05 Jan 2011 16:10:52 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxHi 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 = 1The job falls over when it comes to execute the sp_send_dbmail command.Wed, 05 Jan 2011 15:33:57 GMTJayKhttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxbased 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...Wed, 05 Jan 2011 15:10:38 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxI 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.aspxdid anyone else have the same issue?Wed, 05 Jan 2011 14:43:57 GMTJayKhttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxsure, 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...Wed, 05 Jan 2011 13:06:32 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxGeoff,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..ThanksWed, 05 Jan 2011 12:52:57 GMTgmamata7http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxI 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 '[b]%deadlock-list%'[/b] order by Id DESC)thanksWed, 05 Jan 2011 12:37:48 GMTgmamata7http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxI ran the below code, but got NO results[quote]IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null BEGIN DROP TABLE tempdb.dbo.ErrorLog ENDCREATE 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_readerrorlogselect 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[/quote][b]Results:[/b]logdate procinfo ERRORLOGNote: I do have the Deadlock graph in SQL Server errorlog. but the above query is not fetching that deadlock details.Please adviceWed, 05 Jan 2011 12:35:55 GMTgmamata7http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxwhat happens if you just run this code?see attachment....Wed, 05 Jan 2011 11:32:14 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspx[quote]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[/quote]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 attachmentWed, 05 Jan 2011 11:16:08 GMTgmamata7http://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxyes, 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?Thu, 16 Dec 2010 14:33:57 GMTGeoff Ahttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxDoesn'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.Tue, 14 Dec 2010 13:53:58 GMTshotmanhttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspxGreat 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?logdate procInfo ERRORLOG ----------------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------(0 rows affected)Tue, 14 Dec 2010 13:02:28 GMTArman Khanhttp://www.sqlservercentral.com/Forums/Topic1033380-2831-1.aspx[quote][b]chuckh 3191 (12/14/2010)[/b][hr]I'm trying to remember. Do you have to turn back on DBCC TRACEON each time you restart the instance?[/quote]yes.from the article - [i]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.[/i]Tue, 14 Dec 2010 08:33:22 GMTGeoff A