Getting an email notification when a deadlock is occured

If you are administering a heavy transactional database and often facing an issue with incomplete transactions, deadlocks might be the issue and it needs to be detected. There are few ways of checking whether a deadlock is occurring nor not, the most common one is the Profiler. However, it is bit difficult to keep on running the Profiler (or even Traces) against a production database and monitoring it, hence tracking it when it occurs and notifying is the best.

The easiest the method to track it is, using an Alert. In addition to that, it needs Database Mail configured, an Operator, and a change in sys.messages table.

• Database mail - An implementation of SMTP to enable database component to send email.
• Operator -A person or group who can receive notification based on a job or an alert raised.
• Alert - is a predefined response to an event.

Let's configure all and test this. Database mail can be set creating a profile and adding a SMTP account. It can be simply configured using Database Mail under Management in Object Explorer. Note that, for this example, I am using gmail as my SMTP server.

After configuring database mail, configure an operator accessing Operators under SQL Server Agent.

Before configuring an Alert, we need to do a modification to record in a sys.messages table. This table maintains all messages related to errors and it has a column called is_event_logged that describes whether the error related is getting written to Application Log or not. SQL Server raises Alerts only when it is notified by Application Log with a message and matched with a configured Alert. Therefore we need to make sure that error needs to be tracked is logged. By default, deadlock error message, which is 1205 is not getting logged, hence it needs to be changed.

USE master;
GO

-- checking the record
SELECT * FROM sys.messages WHERE message_id = 1205 and language_id = 1033;

-- setting is_event_logged to 1
EXEC dbo.sp_altermessage 1205, 'WITH_LOG', 'true';

-- checking the updated record
SELECT * FROM sys.messages WHERE message_id = 1205 and language_id = 1033;

Now let's create an Alert and sets the Response to the Operator created.

Everything required is done now. Whenever a deadlock occurs, it will be written the Application Log and it noifies Agent and then Agent will fire the Alert.