First - a disclaimer. This method does not email the deadlock graph to you, only an alert saying that the deadlock has occurred. It is assumed that you are running a server-side trace to capture the necessary information for trouble-shooting. This is only to give you the alert.
- Assure that the 1205 message is actually logged. (very good info on this in Michael K. Campbell's article for Practical SQL Server)
EXEC master..sp_altermessage 1205, 'WITH_LOG', TRUE;
GO
- Assure that SQLMail is configured
- NOTE: The step I forget most often is to assure that mail is enabled under SQL Server Agent -> Properties, as is outlined here.
- Create an alert for 1205 message.
- Test with scripts found here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49692
- NOTE: This will only set up the notification. You also have to configure a default monitor to detect deadlocks. The mail will only prompt you to go and have a look at the deadlock trace!
References used in compiling this procedure:
Info on the error message I encountered in using sqlmail with alerts:
Error: An attempt was made to send an email when no email session has been established
Link ; http://www.sqldbadiaries.com/2010/07/27/an-attempt-was-made-to-send-an-email-when-no-email-session-has-been-established-sql-server-2005/
Procedure for setting up alerts:
http://sqlmag.com/blog/enabling-email-alerts-sql-server-deadlocks
Test Script to simulate deadlock:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49692