SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example,
If I decide to monitor any dead lock in the database, I just query the trace file to see the history of deadlock for a period of time. Here is my question:
While events occur, in our example deadlock event, is there a way to get an email notification using msdb.dbo.xp_send_dbmail?
Best Answer
Yes you can do that using Event Notification for deadlocks / blocking / create database / drop database, and many more events as outlined here.
Below is the script that will help you with Deadlock detection and email in real time :
It will create an alert as well as a TSQL Job to fire with all the details emailed to DBA team. look for change Here to replace required stuff.