Monday, December 13, 2010

How to receive Deadlock information automatically via email.

http://www.sqlservercentral.com/articles/deadlock/71374/


Requirement number one:
The first requirement is to turn on the appropriate Trace Flags. We do that by running the following command.

DBCC TRACEON (3605,1204,1222,-1)

A brief overview to what those flags do is listed below.
3605 = write what we want to the error log.
1204 = Capture Deadlock Events.
1222 = Capture Deadlock Events with more info (SQL 2005 and higher)

It is important to note that setting trace flags this way will only work until the next time SQL Server is restarted. If you want to make sure your trace flags are always set this way, you can edit the startup options of SQL Server by using the -T switch or creating a SQL Agent job to run that code when the Agent starts.

Using SQL Profiler also can find the dead lock.

No comments:

Post a Comment