SQLServerCentral / Discuss Content Posted by Patrick LeBlanc / Article Discussions / Article Discussions by Author / Deadlock Notifications in SQL Server 2005 / Latest Posts

RE: Deadlock Notifications in SQL Server 2005

sqldba-294117 — Thu, 29 Sep 2011 16:12:12 GMT

I have made minor change to that code as shown below & shorten with few lines.Create this store proc and schedule in SQL Agent Job for 10mins. it will only email if there is deadlock.sp_send_dbmail as limitation that local variable cannot be pass within @query, but global variable helps & dropping at the end.CREATE PROC [dbo].[usp_DeadlockNotification]ASCREATE TABLE ##ErrorLog( LogDate DATETIME NOT NULL, ProcessInfo VARCHAR(15), text VARCHAR(15))DECLARE @StartDate DATETIME,@EndDate DATETIMEDECLARE @Minutes INTSET @Minutes = 10 SELECT @EndDate = GETDATE() SELECT @StartDate = DATEADD(mi, -@Minutes, @EndDate) INSERT INTO ##Errorlog EXEC master.dbo.xp_readerrorlog 0, 1,'deadlock-list',NULL, @StartDate, @EndDate, N'desc'Declare @text varchar(15)SET @text = (select 1 from ##Errorlog where text = 'deadlock-list' )print @textselect * from ##ErrorlogIF (@text = 1)EXEC msdb.dbo.sp_send_dbmail @profile_name = SQL DBA', @recipients = 'SQLDBA@abc.com', @subject = 'Deadlock Encounter', @attach_query_result_as_file = 1,@query = 'BEGINselect * from ##Errorlog END'ELSE SELECT 'No deadlock found!'DROP Table ##Errorlog

RE: Deadlock Notifications in SQL Server 2005

Patrick_LeBlanc — Mon, 31 Aug 2009 09:27:02 GMT

Not without searching extensively through the error log. I would suggest using the Profiler or as you stated in your posts events, specifically extended events if you are running SQL 2008. If you would like to talk further about this please feel free to email me a pleblanc @tsqlscripts and I am sure we can figure something out.

RE: Deadlock Notifications in SQL Server 2005

DBA-640728 — Thu, 27 Aug 2009 14:58:28 GMT

hi is there a way to add the deadlock graph information from the log into your email notification without having to create an event?

RE: Deadlock Notifications in SQL Server 2005

digivince — Thu, 05 Mar 2009 13:29:43 GMT

Such complexity to handle something built-in and simple to configure? I concur... use alerts instead!USE [msdb]GO--create "Operator" with customer's emailEXEC msdb.dbo.sp_add_operator @name=N'AppSupport', @enabled=1, @email_address=N'customer@emailthem.com', @category_name=N'[Uncategorized]'GO--create deadlock alertEXEC msdb.dbo.sp_add_alert @name=N'DeadLocks', @message_id=0, @severity=0, @enabled=1, @delay_between_responses=5, @include_event_description_in=1, @notification_message=N'SQL Server deadlock alert.', @category_name=N'[Uncategorized]', @performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0', @job_id=N'00000000-0000-0000-0000-000000000000'GO

RE: Deadlock Notifications in SQL Server 2005

Patrick_LeBlanc — Fri, 29 Feb 2008 07:29:14 GMT

Try this one:IF OBJECT_ID( 'dbo.usp_DeadlockNotification') IS NOT NULL DROP PROC dbo.usp_DeadlockNotificationGOCREATE PROC dbo.usp_DeadlockNotification @FilterBIT int = 0, @Minutes INT = 30ASDECLARE @ErrorLog TABLE ( LogDate DATETIME NOT NULL, ProcessInfo VARCHAR(75), LogInfo VARCHAR(MAX))DECLARE @Count INT, @StartDate DATETIME, @EndDate DATETIMESET @Count = 0SET NOCOUNT ON-- Step I: Import ErrorlogINSERT INTO @Errorlog EXEC xp_readerrorlog ---- Step II: How to search ErrorlogIF (@FilterBIT <> 0)BEGIN SELECT @EndDate = GETDATE() SELECT @StartDate = DATEADD(mi, -@Minutes, @EndDate) SELECT @Count = COUNT(*) FROM @Errorlog WHERE LogDate BETWEEN @StartDate AND @EndDate AND LogInfo LIKE '%Deadlock%'ENDELSEBEGIN SELECT @Count = COUNT(*) FROM @Errorlog WHERE LogInfo LIKE '%Deadlock%'END---- Step III: Send EmailIF (@Count > 0)BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLTestCluster', @recipients = 'pleblanc@lamar.com', @subject = 'Deadlocks', @body = 'Please check errorlog for Deadlocks'END

RE: Deadlock Notifications in SQL Server 2005

GarnetR — Thu, 28 Feb 2008 12:27:21 GMT

When I cut and pasted the first procedure into a new query window and attempted to execute, I got a whole series of syntax errors:Msg 102, Level 15, State 1, Procedure usp_DeadlockNotification, Line 5Incorrect syntax near '='.Msg 137, Level 15, State 2, Procedure usp_DeadlockNotification, Line 28Must declare the scalar variable "@Filter".Msg 137, Level 15, State 2, Procedure usp_DeadlockNotification, Line 31Must declare the scalar variable "@Minutes".Msg 156, Level 15, State 1, Procedure usp_DeadlockNotification, Line 38Incorrect syntax near the keyword 'ELSE'.

RE: Deadlock Notifications in SQL Server 2005

Patrick_LeBlanc — Thu, 11 Oct 2007 07:34:03 GMT

Thanks for such a great blog.

RE: Deadlock Notifications in SQL Server 2005

Malcolm Daughtree — Wed, 10 Oct 2007 16:37:32 GMT

Thanks Patrick, There are many ways to this end. Yours.... simple....effective....to the point.Awesome !!CodeOn :P

RE: Deadlock Notifications in SQL Server 2005

Robert Davis — Wed, 10 Oct 2007 16:05:55 GMT

Great article!! And thanks for the plug of my Blog. :smooooth:

RE: Deadlock Notifications in SQL Server 2005

Patrick_LeBlanc — Wed, 10 Oct 2007 07:56:07 GMT

Yes you can use event notfications, and thanks for the link.

RE: Deadlock Notifications in SQL Server 2005

Will SQL 4 Food — Wed, 10 Oct 2007 07:45:11 GMT

Great article!As for the DMV, I concur with LP. The sys.dm_os_performance_counters view is constantly incrementing various counters of performance that SQL Server reports to the O/S. As written, after the first deadlock occurs the proc will signal an e-mail every time the job runs. If you capture the results at the last run of the sproc, then compare the current run against those values you'll have an accurate measure of deadlocks in the interval between job runs. The you can signal an e-mail only when there's an increase in the counter since the last run.As for the volatility of the values in the DMV, they are all reset to 0 with a reboot or an instance restart.Thanks for the article, and I hope this helps!Carter

RE: Deadlock Notifications in SQL Server 2005

antxxxx — Wed, 10 Oct 2007 07:24:35 GMT

why not just use event notfications that are available in sql 2005? If you monitor the deadlock graph event you can get an alert that contains information about the deadlock. The alert can be a realtime email or just storing the data in a table which you can check once a day.This is a lot easier than writing a job which checks the details every minute or half an hour and contains all of the information you need to track down where the problem is.Tony Rogerson has written an excellent article on to set up event notifications for blocking events at http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspxIt fairly easy to change that to report on deadlock_graph event instead

RE: Deadlock Notifications in SQL Server 2005

DBA_Rob — Wed, 10 Oct 2007 07:18:03 GMT

I haven't tried it yet, but could you also set up an Alert in SQL Server 2005 where the Object is SQL Server:Locks and the Counter is Number of Deadlocks/sec with a condition of if rises above 0. Then you could subsrcibe to the Alert?

RE: Deadlock Notifications in SQL Server 2005

LP-181697 — Wed, 10 Oct 2007 06:44:56 GMT

"For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used."It means that initially you may have some not 0 values and the proc dbo.usp_DeadlockNotification will gives the alerts each time.You should use difference in time interval.

RE: Deadlock Notifications in SQL Server 2005

Bogdan Dumitru — Wed, 10 Oct 2007 04:03:54 GMT

Try this stored procedure:(where PerfDB is my database for differend kind of administrative task)and make a job with that stored procedure, and run this job at every minute, and you will have a real time monitor of bloking.USE [PerfDB]GO/****** Object: StoredProcedure [dbo].[BD_Monitorizare_Blocaje] Script Date: 10/10/2007 12:59:36 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE procedure [dbo].[BD_Monitorizare_Blocaje] as declare @spid int declare @blk_by int declare @spid2 int declare @buffer1 varchar(8000) declare @buffer2 varchar(8000) declare @sql1 varchar(8000) declare @sql2 varchar(8000) declare @login1 varchar(50) declare @login2 varchar(50) declare @database_name varchar(20) declare @dbid int declare @sql5 varchar(8000) declare @sql6 varchar(8000) declare @hostname1 varchar(100) declare @hostname2 varchar(100) declare @program_name1 varchar(100) declare @program_name2 varchar(100) create table #BD_Monitorizare_Procese ( spid varchar(50), login varchar(100), hostname varchar(100), program_name varchar(100), blk_by varchar(100), dbid varchar(100), last_batch datetime ) insert into #BD_Monitorizare_Procese select spid ,convert(sysname, rtrim(loginame)) ,hostname ,program_name ,blocked ,dbid , last_batch from master.dbo.sysprocesses with (nolock) where len(hostname)>0 DECLARE xcursor CURSOR FOR SELECT spid FROM #BD_Monitorizare_Procese order by spid OPEN xcursor FETCH NEXT FROM xcursor INTO @spid WHILE (@@fetch_status = 0) BEGIN select @blk_by = blk_by from #BD_Monitorizare_Procese where spid=@spid if @blk_by <> 0 begin select @login1 = login from #BD_Monitorizare_Procese where spid=@spid select @login2 = login from #BD_Monitorizare_Procese where spid=@blk_by select @dbid = dbid from #BD_Monitorizare_Procese where spid=@spid select @database_name = name from sys.sysdatabases where dbid=@dbid select @hostname1 = hostname from #BD_Monitorizare_Procese where spid=@spid select @hostname2 = hostname from #BD_Monitorizare_Procese where spid=@blk_by select @program_name1 = program_name from #BD_Monitorizare_Procese where spid=@spid select @program_name1 = program_name from #BD_Monitorizare_Procese where spid=@blk_by create table #dbcc(c1 varchar(20), c2 int, c3 varchar(8000)) insert #dbcc EXEC('dbcc inputbuffer('+@spid+')') select @buffer1 = c3 from #dbcc delete from #dbcc insert #dbcc EXEC('dbcc inputbuffer('+@blk_by+')') select @buffer2 = c3 from #dbcc drop table #dbcc set @buffer1 = replace(@buffer1,'''','`') set @buffer2 = replace(@buffer2,'''','`' ) ----declare @buffer1 varchar(8000) --declare @buffer2 varchar(8000) --declare @sql1 varchar(8000) --declare @sql2 varchar(8000) --declare @login1 varchar(50) --declare @login2 varchar(50) --declare @database_name varchar(20) --declare @dbid int --declare @sql5 varchar(8000) --declare @sql6 varchar(8000) --declare @hostname1 varchar(100) --declare @hostname2 varchar(100) --declare @program_name1 varchar(100) --declare @program_name2 varchar(100) --set @database_name='fffff'--set @login1='ddddd'--set @login2='gggggg'--set @hostname1='hhhhhhh'--set @hostname2='sssss'--set @buffer1='423423423324'--set @buffer2='55345323552' exec('exec PerfDB.dbo.BD_Send_Alert ''Este un blocaj pe baza '+@database_name+'. Userul '+@login1+' de pe statia '+@hostname1+' este blocat de '+@login2+' de pe statia '+@hostname2+' ! '+'Userul '+@login1+' executa: '+@buffer1+'. Userul '+@login2+' executa: '+@buffer2+'' +''''+ ','' BLOCAJ !!!''') --print 'exec PerfDB.dbo.BD_Send_Alert ''Este un blocaj pe baza '+@database_name+'. Userul '+@login1+' de pe statia '+@hostname1+' este blocat de '+@login2+' de pe statia '+@hostname2+' ! '+'Userul '+@login1+' executa: '+@buffer1+'. Userul '+@login2+' executa: '+@buffer2+'' +''''+ ','' BLOCAJ !!!'''-- set @sql1= 'EXEC master..xp_cmdshell ''net send CBOGDUM '''' Este un blocaj pe baza '+@database_name+' userul '+@login1+' este blocat de '+@login2+' !!''' -- exec (@sql1) -- -- set @sql2= -- 'EXECUTE [msdb].[dbo].[sp_send_dbmail] -- @profile_name = ''MailAccount'' -- ,@recipients = ''bogdan.dumitru@railoc.ro'' -- ,@body = '' Este un blocaj pe baza '+@database_name+'! Userul '+@login1+' de pe statia '+@hostname1+' este blocat de '+@login2+' de pe statia '+@hostname2+' ! '+char(13)+char(13)+'Userul '+@login1+' executa: '+char(13)+@buffer1+char(13)+char(13)-- +' Userul '+@login2+' executa: '+char(13)+@buffer2+''' -- ,@subject = ''BLOCAJ!''' -- exec (@sql2) insert into BD_Monitorizare_Blocaje_tbl ( database_name, login1_blocat, login2_care_blocheaza, hostname_login1, hostname_login2, buffer_login1, buffer_login2 ) values (@database_name, @login1, @login2, @hostname1, @hostname2, @buffer1, @buffer2 ) FETCH NEXT FROM xcursor INTO @spid end else FETCH NEXT FROM xcursor INTO @spid END CLOSE xcursor DEALLOCATE xcursor --create table BD_Monitorizare_Blocaje_tbl--(--data datetime default getdate(),--database_name varchar(20),--login1_blocat varchar(50),--login2_care_blocheaza varchar(50),--hostname_login1 varchar(50),--hostname_login2 varchar(50),--buffer_login1 varchar(8000),--buffer_login2 varchar(8000),--program_name1 varchar(100),--program_name2 varchar(100)--)

Deadlock Notifications in SQL Server 2005

Patrick_LeBlanc — Tue, 04 Sep 2007 17:29:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/pleblanc/3243.asp