How to receive Deadlock information automatically via email.

  • edynak_vova

    Newbie

    Points: 9

    It's a really great post!!!!!!!:Wow: I even did not realze that it's possible to automate logging of the deadlock information using sp_readerrorlog and alerts. I thought that it is only possible using event notification (using queues with activation procedure). Do you know are there any additional information about deadlock due to the trace flag 1222 in comparison with the info that comes from the event notifications? What additional information about deadlock can be obtained using dbcc traceon (1222)?

  • jhopkins 13208

    Grasshopper

    Points: 12

    Try a Service Broker based Event Notification. It's elegant and I find it works well.

  • sqldba.today

    SSC Veteran

    Points: 235

    I agree, Event Notifications work for Deadlocks and so much more. Here's my blog posting on how I created a "generic" event notification service for all of my SQL servers. It alerts on database creation and drops, login creations, role changes, etc.

    http://www.the-fays.net/blog/?p=239

    No trace flags, no agent jobs, no temp tables, no WMI calls. I'd recommend that anyone reading it, take strong consideration to the updates about using certificates rather than changing the TRUSTWORTHY setting on the database used.

  • javedazm

    Newbie

    Points: 9

    Hi,

    very good post..

    But when deadlock occured i recived attachment in text file but sadly it didn't contain any data.

    Is there something in select query? coz i cross check data inserted in a table propely but slect query didn't get any data degarding deadlock

  • syed_abbas

    SSC Rookie

    Points: 27

    adding (and logdate = GETDATE()) will send current date locks, otherwise it keeps sending old ones as well Thanks

  • SQL Galaxy

    One Orange Chip

    Points: 29249

    Hi.. it is very useful script for monitoring deadlock event occur in OLTP databases.

    I am facing same issues: But when deadlock occurred i received attachment in text file but sadly it didn't contain any data.

    I enabled 1222 -1 trace flag in SQL 2012 instance.

    Thanks

  • Tony Trus

    SSChasing Mays

    Points: 654

    If you are in SQL 2012 I would use extended events. That said, the reason you are not getting information in the email is because the error log changed its wording so the line to change would be listed below.

    Depending on the version of SQL you would need to choose between these options:

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

    or

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

  • SQL Galaxy

    One Orange Chip

    Points: 29249

    Thanks Tony for your reply, I have tried and modified on existing script for file attachment as below, but its throwing errors

    Please help.

    declare @subject varchar(250)

    select @subject = 'Deadlock reported on ' + @@servername

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'db_mail',

    @recipients='email_id@abb.com',

    @subject = @subject,

    @body = 'A deadlock has been recorded. Further information can be found in the attached file.',

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

    @query_result_width = 600,

    @attach_query_result_as_file = 1

    Msg 22050, Level 16, State 1, Line 0

    Error formatting query, probably invalid parameters

    Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 517

    Query execution failed: ?Msg 102, Level 15, State 1, Server SABBPROD034, Line 1

    Incorrect syntax near 'deadlock

    Existing script as below which is currently in SQL Job at step

    if object_id('tempdb..##error') is not null drop table ##error

    go

    create table ##error

    (

    id int identity(1, 1),

    logDate datetime,

    processInfo varchar(20),

    errorText nvarchar(max)

    )

    insert into ##error

    exec master.dbo.sp_readErrorLog

    select logDate, processInfo, errorText

    from ##error

    where id >=

    (

    select max(id)

    from ##error

    where errorText like '%deadlock encountered%'

    )

    declare @subject varchar(250)

    select @subject = 'Deadlock reported on ' + @@servername

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'db_mail',

    @recipients='email_id@abb.com',

    @subject = @subject,

    @body = 'A deadlock has been recorded. Further information can be found in the attached file.',

    @query = 'select logDate, processInfo, errorText

    from ##error

    where id >=

    (

    select max(id)

    from ##error

    where errorText like ''%deadlock encountered%''

    )',

    @query_result_width = 600,

    @attach_query_result_as_file = 1

    drop table ##error

  • Tony Trus

    SSChasing Mays

    Points: 654

    Note there are four quotes on each side as its dynamic SQL and yours has two which would throw errors. That should fix

  • SQL Galaxy

    One Orange Chip

    Points: 29249

    The same error repeated after removed quotes on both side..

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

Viewing 10 posts - 61 through 70 (of 70 total)

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