Capturing Deadlock in details using SQL Server trace and performance monitor…


SQL Server captures basic deadlock information and victim transaction experiences error. However you may need to capture details about deadlock for corrective actions. To provide you with such capability, Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server Profiler.

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server 2005 error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. On the other hand, trace flag 1222 formats deadlock information, first by processes and then by resources.

It is recommended to enable both trace flags to obtain two representations of the same deadlock event while you are actively working to troubleshoot a deadlock related problem. Also capturing deadlock details graphically using performance monitor may be beneficial.

If you want to refer more details, please refer to http://msdn.microsoft.com/en-us/library/ms178104.aspx

Comments

Popular Posts