Friday, March 29, 2013

Enable Trace Flag 1222

Deadlock graph can be detected from SQL Profiler client tool as explained here. This approach requires to keep the client tool running during monitoring periods, and it will probably be ideal for intensive monitoring work. If deadlock occurs seldom but we don't want to miss the deadlock details, you might want to enable SQL Server engine trace flag 1222.

Trace flag 1222 outputs deadlock detail information to SQL Server Error log. It dumps the deadlock details in text format, so there is no fancy UI as in SQL Profiler. But the log contains all the same information as in SQL Profiler Deadlock Graph event, so some extra detective analysis will give deadlock picture.

To enable trace flag 1222, go to SQL Server Configuration Manager (assuming SQL 2008).
Click SQL Server Services on the left tree and doubeclick SQL Server instance.
Go to Advanced tab and find [Startup Parameters].
Add ";-T1222" and click OK.
Semicolon is needed to separate each parameter and -T option means SQL engine trace flag.

One drawback(?) of using SQL Server trace flag is that you need to restart SQL Server services to take effect. So in production server, maintenance should be scheduled properly.

No comments:

Post a Comment