Deadlock Notifications in SQL Server 2005

  • Comments posted here are about the content posted at

  • 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]


    /****** Object: StoredProcedure [dbo].[BD_Monitorizare_Blocaje] Script Date: 10/10/2007 12:59:36 ******/





    CREATE procedure [dbo].[BD_Monitorizare_Blocaje]


    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



    ,convert(sysname, rtrim(loginame))





    , last_batch

    from master.dbo.sysprocesses with (nolock)

    where len(hostname)>0


    SELECT spid FROM #BD_Monitorizare_Procese order by spid

    OPEN xcursor

    FETCH NEXT FROM xcursor INTO @spid

    WHILE (@@fetch_status = 0)


    select @blk_by = blk_by from #BD_Monitorizare_Procese where spid=@spid

    if @blk_by <> 0


    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 = ''''

    -- ,@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



    FETCH NEXT FROM xcursor INTO @spid


    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)


  • "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.

  • 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?

  • 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

    It fairly easy to change that to report on deadlock_graph event instead

  • 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!


    But boss, why must the urgent always take precedence over the important?

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

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

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Thanks Patrick, There are many ways to this end. Yours.... the point.

    Awesome !!

    CodeOn 😛

  • Thanks for such a great blog.

  • 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 5

    Incorrect syntax near '='.

    Msg 137, Level 15, State 2, Procedure usp_DeadlockNotification, Line 28

    Must declare the scalar variable "@Filter".

    Msg 137, Level 15, State 2, Procedure usp_DeadlockNotification, Line 31

    Must declare the scalar variable "@Minutes".

    Msg 156, Level 15, State 1, Procedure usp_DeadlockNotification, Line 38

    Incorrect syntax near the keyword 'ELSE'.

  • Try this one:

    IF OBJECT_ID( 'dbo.usp_DeadlockNotification') IS NOT NULL

    DROP PROC dbo.usp_DeadlockNotification


    CREATE PROC dbo.usp_DeadlockNotification

    @FilterBIT int = 0,

    @Minutes INT = 30





    ProcessInfo VARCHAR(75),

    LogInfo VARCHAR(MAX)


    DECLARE @Count INT,

    @StartDate DATETIME,

    @EndDate DATETIME

    SET @Count = 0


    -- Step I: Import Errorlog

    INSERT INTO @Errorlog

    EXEC xp_readerrorlog

    ---- Step II: How to search Errorlog

    IF (@FilterBIT <> 0)


    SELECT @EndDate = GETDATE()

    SELECT @StartDate = DATEADD(mi, -@Minutes, @EndDate)

    SELECT @Count = COUNT(*)

    FROM @Errorlog

    WHERE LogDate BETWEEN @StartDate AND @EndDate

    AND LogInfo LIKE '%Deadlock%'




    SELECT @Count = COUNT(*)

    FROM @Errorlog

    WHERE LogInfo LIKE '%Deadlock%'


    ---- Step III: Send Email

    IF (@Count > 0)


    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SQLTestCluster',

    @recipients = '',

    @subject = 'Deadlocks',

    @body = 'Please check errorlog for Deadlocks'


  • Such complexity to handle something built-in and simple to configure? I concur... use alerts instead!

    USE [msdb]


    --create "Operator" with customer's email

    EXEC msdb.dbo.sp_add_operator @name=N'AppSupport',





    --create deadlock alert

    EXEC msdb.dbo.sp_add_alert @name=N'DeadLocks',






    @notification_message=N'SQL Server deadlock alert.',


    @performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0',



    Columbus, GA SQL Server User Group

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

  • 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.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply