locked
Deadlock email RRS feed

  • Question

  • Hello,

    I am using SQL Server 2012 SE and I am noticing a lot of deadlocks that are occurring more frequently on few databases on a SQLServer. I enabled traceflag 1222 to capture the info in the SQL Server Error Log. However I am thinking to have sqlserver send me an email with deadlock victim sql and culprit sql and also other deadlock information whenever a deadlock occurs.

    I know there is another way to track deadlocks with extended events in sqlserver by expanding folder extended events -->sessions-->system_health-->package0.event_file right click target data and look for name XML_Deadlock_report shows me the deadlock graph with details and with timestamp.

    Is there a way to get the email with complete sql statements for culprit and victim without having impact on performance/minimal impact on performance.

    Thanks for your inputs.

    Sunday, June 21, 2015 4:30 AM

Answers

  • can send test mail and check email is working fine..

    1)select * from msdb.dbo.sysmail_allitems

    2)SELECT      description FROM sysmail_event_log

    ORDER BY    log_date DESC


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue


    Wednesday, June 24, 2015 3:49 PM
  • SELECT      description FROM sysmail_event_log

    ORDER BY    log_date DESC


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    Wednesday, June 24, 2015 7:54 PM

All replies

  • http://sqlmag.com/blog/enabling-email-alerts-sql-server-deadlocks

    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    • Proposed as answer by Visakh16MVP Sunday, June 21, 2015 6:03 AM
    • Unproposed as answer by oleolehoohoo Wednesday, June 24, 2015 3:00 PM
    Sunday, June 21, 2015 4:33 AM
  • That didnt quite help. Anything with extended events and service brokering?
    Wednesday, June 24, 2015 3:02 PM
  • This will help you

    http://sqlmag.com/site-files/sqlmag.com/files/archive/sqlmag.com/content/content/142603/wpd-sql-extevtandnotif-us-sw-01112012_1.pdf


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue


    • Edited by AV111 Wednesday, June 24, 2015 3:33 PM
    Wednesday, June 24, 2015 3:33 PM
  • I found that white paper as well. And changed the database names to Adventureworks2012 in the scripts form the white paper and executed them. However I was able to create deadlocks in adventureworks2012 database but I was not getting emails or alerts.

    I dont know but may be the code needs tweaking.

    Wednesday, June 24, 2015 3:40 PM
  • can send test mail and check email is working fine..

    1)select * from msdb.dbo.sysmail_allitems

    2)SELECT      description FROM sysmail_event_log

    ORDER BY    log_date DESC


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue


    Wednesday, June 24, 2015 3:49 PM
  • Yep. I am getting emails for my other jobs without issues. 
    Wednesday, June 24, 2015 6:18 PM
  • SELECT      description FROM sysmail_event_log

    ORDER BY    log_date DESC


    Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue

    Wednesday, June 24, 2015 7:54 PM