Hi Friends,

This is my 15th blog on SQL Server Trace Flag 1204 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.

Deadlock is a very common problem for all the DBA. First phase of deadlock resolution is Deadlock detection. So for deadlock detection, Microsoft provides a trace flag 1204 that can write deadlock information to sql server error log. You can analyze that information later to prevent it in future.

Trace flag 1204 provides node base information about deadlock in another words you can say that all nodes which are involved in deadlock. Finally after all nodes information it also provides information about deadlock victim.

Now I’ll show you the output of trace flag 1204 by creating a deadlock. Below script will only creates database, tables and also insert data into them. It will also do error log recycle and enable the trace flag 1204.

Now open a new query window (first session) in management studio and run below query:

Again open a new query window (second session) in management studio and run below query:

In my case, first session completed successfully while second session selected as deadlock victim. Second session gave me below as output.

(1 row(s) affected)

Msg 1205, Level 13, State 45, Line 11
Transaction (Process ID 54) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.


Due to trace flag 1204, you can read the detailed information from sql server error log now.

Now you can see that it provides the node base information along with victim information in the last.

Finally, don’t forget to turnoff the trace flag 1204 here during this test scenario.

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.


Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook