Deadlock Notifications in SQL Server 2005

  • Patrick_LeBlanc

    Ten Centuries

    Points: 1165

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

  • Bogdan Dumitru

    Valued Member

    Points: 73

    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 ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE 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)

    --)

  • LP-181697

    SSC Eights!

    Points: 966

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

  • DBA_Rob

    Default port

    Points: 1493

    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?

  • antxxxx

    SSC Rookie

    Points: 37

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

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

  • Will SQL 4 Food

    SSCrazy

    Points: 2223

    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



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

  • Patrick_LeBlanc

    Ten Centuries

    Points: 1165

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

  • Robert Davis

    One Orange Chip

    Points: 28027

    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]

  • Malcolm Daughtree

    SSCrazy

    Points: 2373

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

    Awesome !!

    CodeOn 😛

  • Patrick_LeBlanc

    Ten Centuries

    Points: 1165

    Thanks for such a great blog.

  • GarnetR

    Grasshopper

    Points: 17

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

  • Patrick_LeBlanc

    Ten Centuries

    Points: 1165

    Try this one:

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

    DROP PROC dbo.usp_DeadlockNotification

    GO

    CREATE PROC dbo.usp_DeadlockNotification

    @FilterBIT int = 0,

    @Minutes INT = 30

    AS

    DECLARE @ErrorLog TABLE

    (

    LogDate DATETIME NOT NULL,

    ProcessInfo VARCHAR(75),

    LogInfo VARCHAR(MAX)

    )

    DECLARE @Count INT,

    @StartDate DATETIME,

    @EndDate DATETIME

    SET @Count = 0

    SET NOCOUNT ON

    -- Step I: Import Errorlog

    INSERT INTO @Errorlog

    EXEC xp_readerrorlog

    ---- Step II: How to search Errorlog

    IF (@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%'

    END

    ELSE

    BEGIN

    SELECT @Count = COUNT(*)

    FROM @Errorlog

    WHERE LogInfo LIKE '%Deadlock%'

    END

    ---- Step III: Send Email

    IF (@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

  • digivince

    SSC-Addicted

    Points: 420

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

    USE [msdb]

    GO

    --create "Operator" with customer's email

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

    @enabled=1,

    @email_address=N'customer@emailthem.com',

    @category_name=N'[Uncategorized]'

    GO

    --create deadlock alert

    EXEC 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

    Columbus, GA SQL Server User Group
    http://columbusga.sqlpass.org/

  • DBA-640728

    SSChampion

    Points: 12896

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

  • Patrick_LeBlanc

    Ten Centuries

    Points: 1165

    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 16 total)

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