Checking deadlock in the ERRRLOG after enabling the trace

First, to check if a specific trace is enabled on SQLServer, the following command can be used. If this command does not return any result, then there were no traces enabled by DBA.

DBCC TRACESTATUS();

To turn on the trace for detecting the deadlock in the ERRORLOG, the traces (1204, 1222) can be enabled using the commands:

DBCC TRACEON (1204, -1);

DBCC TRACEON (1222, -1);

Then, using the SQLServerManagement Console, you can keep on checking the ERRORLOG, to find if there are any deadlock occurred on not.

If the ERRORLOG is too big, the search process will choke the performance of the database server. To avoid that. The ERRORLOGs can be copied to another server and the following command can be used to find the lines that displays deadlock related information.

findstr “deadlock”  ERRORLOG

Here, deadlock is the search string and the ERRORLOG is filename.

Once you find the deadlock related information, the traces can be turned off using the following commands:

DBCC TRACEOFF (1204, -1);

DBCC TRACEOFF (1222, -1);