How to Detect Deadlocks from SQL Profiler

Deadloack detection is a very common requirement in Development (even in Production) environment. There are couple of method to detect Dead Locks. In SQL Server 2000, trace flag 1204 can be set to capture the processes involved in the deadlock. The output is text based but provides valuable information about the types of locks and the statements that were executing at the time of the deadlock. In addition to this approach, SQL Server 2005 offers the ability to capture detailed deadlock information via the SQL Server Profiler. The process of Dead Lock detection in SQL Server 2005 is as follows

(a) Create a new trace, Select a Blank template; this leaves the selection of all the events, data columns, and filters to you.

(b) Add the Locks:Deadlock graph event to the trace from the Locks category. An additional tab appears on the Trace Properties window, named Event Extraction Settings.

(c) Click the Save Deadlock XML Events Separately check box. This causes the deadlock information to be written to a separate file. You could also export the results after the trace has been run by using the File, Export option.