How to create email alerts for extended events?

Question

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?

Answer 1

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.

6. Store the deadlock graph in a table.
7. Retrieve the cached plans associated with the deadlock in another table.
8. 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