SQL Server 2005/2008: Capturing Deadlock information or simulating deadlock

You can always run a profiler trace to capture the deadlock graph that gives details of the SPID and queries involved. But I would rather turn on the traces for deadlock and capture the info in the Error logs.

To do this run the following commands

DBCC TRACEON (1204, 01)

DBCC TRACEON (1222, -1)

The output from each of them are different so check the output before you decide which one you prefer. I like the output from the second one which was introduced in SQL Server 2005 and gives the info for each process involved.

If you want to test the output then take the following steps to simulate a deadlock and check the output.

1) CREATE TABLE a (i int); 

    CREATE TABLE b (i int);

   

    INSERT a SELECT 1;

    INSERT b SELECT 9;

2) In a new window
    BEGIN TRAN

    UPDATE a SET i = 11  

    WHERE i = 1 

    WAITFOR DELAY '00:00:10' 

    UPDATE b SET i = 99 

    WHERE i = 9 

    COMMIT

3) In another window
    BEGIN TRAN 

    UPDATE b SET i = 99 

    WHERE i = 9 

    WAITFOR DELAY '00:00:10' 

    UPDATE a SET i = 11 

    WHERE i = 1 

    COMMIT

If you want to retest the deadlock then drop the tables and rerun the steps.