2

I have created extended events in SQL server 2012. Everything is working fine. Now I am looking for if any events occur (example :deadlock), it should send mail to given mail id. Is it possible in extended events?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vijay Kumar
  • 21
  • 1
  • 2
  • Was my answer useful? – Thiago Avelino Jun 03 '15 at 23:20
  • In general, there's no way I now of [for now](https://connect.microsoft.com/SQLServer/feedback/details/782941/support-sql-broker-service-to-be-a-target-of-extended-events) to respond to an XEvent directly from T-SQL. However, you can handle/respond to XEvents with the QueryableXEventData class in the .NET Framework. [Here's an example](http://itsalljustelectrons.blogspot.com/2017/01/SQL-Server-Event-Handling-Extended-Events.html) – Dave Mason Jan 06 '17 at 18:28

1 Answers1

4

There is a very interesting article about it, basically you need to:

  1. Enable service broker on the database.
  2. Create a service broker queue to receive the event notification messages.
  3. Create a service broker service to deliver event notification messages.
  4. Create a service broker route to route the event notification message to the service broker queue.
  5. Create event notification on deadlock event to create messages and send them to the service broker service

Through service broker, a stored procedure can be written that responds to deadlock events. Event notifications allow deadlock graphs to be transformed, stored, and sent wherever they need to go.

  1. Store the deadlock graph in a table.
  2. Retrieve the cached plans associated with the deadlock in another table.
  3. Email the deadlock graph to the DBA team.

You can find the article with the examples on this link: http://sqlmag.com/site-files/sqlmag.com/files/archive/sqlmag.com/content/content/142603/wpd-sql-extevtandnotif-us-sw-01112012_1.pdf

Pages of reference: 9 - 13

Thiago Avelino
  • 818
  • 6
  • 7
  • Hi, I am looking for only configure email alerting system for extended events. Whenever deadlock occur it should sent alert to emailid is it possible with extended events. – Vijay Kumar Jun 04 '15 at 15:18