Deadlock Notifications in SQL Server 2005

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

    AS

    CREATE TABLE ##ErrorLog

    (

    LogDate DATETIME NOT NULL,

    ProcessInfo VARCHAR(15),

    text VARCHAR(15)

    )

    DECLARE @StartDate DATETIME,@EndDate DATETIME

    DECLARE @Minutes INT

    SET @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 @text

    select * from ##Errorlog

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

    select * from ##Errorlog

    END'

    ELSE

    SELECT 'No deadlock found!'

    DROP Table ##Errorlog

Viewing post 16 (of 15 total)

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