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).
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