Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Finding SQL Server Deadlocks Using Trace Flag 1222

By:   |   Last Updated: 2010-10-05   |   Comments (12)   |   Related Tips: More > Performance Tuning


I have been getting sporadic deadlocks on my SQL Server. How can I track down the queries that are causing the deadlocks so I can resolve the issue?


There are a few ways you can track down queries that are causing deadlocks. For example, you can use the Deadlock Graph as shown in the previous tip SQL Server Profiler Graphical Deadlock Chain. Another solution is using a trace flag to write the deadlock information to the error log. In this tip, we will cover a few ways you can implement trace flag 1222 to do just that.

There are two types of trace flags in SQL Server; global trace flags and session trace flags. Some trace flags work at both the global and session level, and some trace flags only work at the global level. Trace flag 1222 happens to be one of the trace flags that must be set globally.

There are two ways to enable global trace flags. You can enable the trace flag when SQL Server starts by using the -T1222 startup option, or you can use the DBCC TRACEON(1222,-1) command after SQL Server has started. The -1 parameter in the DBCC TRACEON command indicates to SQL Server that this trace flag should be set globally. The benefit of using the -T startup option is that you can ensure the trace flag is enabled even if SQL Server gets restarted. You can set the -T startup parameter using the Advanced tab of the SQL Server Properties window as show below.

set the -T startup parameter using the Advanced tab of the SQL Server Properties window

You can check the status of the trace flag using the DBCC TRACESTATUS (1222, -1) command. You can see by the following results that the trace flag is enabled, and that it is enabled globally. You can turn off the trace flag any time by simply issuing the DBCC TRACEOFF (1222,-1) command.

turn off the trace flag any time by simply issuing the DBCC TRACEOFF (1222,-1) command

Now that we know the trace flag is enabled, all we have to do is wait for a deadlock to get logged and analyze the output.

You can see the partial output of a sample deadlock below. The first thing to note is the "deadlock victim" which is marked first Then the first block in yellow shows the connection that was the deadlock victim. We can see this is SPID 54. The next set of code that is framed in red is the code that SPID 54 was running. Then the second block in yellow shows the other connection that was in the deadlock, which I have highlighted as SPID 55. The last set of code framed in red is the code that SPID 55 was running that caused this deadlock to occur with SPID 54.

You can see the partial output of a sample deadlock

I know this is harder to read than the simple deadlock graph, but it does contain the information we need to analyze the deadlock.

Next Steps

Review the following topics in Books Online for more information on finding and resolving deadlocks.

Last Updated: 2010-10-05

next webcast button

next tip button

About the author
MSSQLTips author Ken Simmons Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Wednesday, March 18, 2015 - 10:41:19 AM - Ivan Back To Top

Thanks, relevant and to the point!

Tuesday, November 25, 2014 - 2:01:13 PM - Eric in Sacramento Back To Top

Very useful info and clear examples, thank you very much.

Friday, April 11, 2014 - 8:34:11 PM - sampath Back To Top

Thanks for this article. I had a small doubt. after trace on where can we find the summary file which you have shown in this article.




Friday, November 29, 2013 - 7:17:22 AM - inder Back To Top

Sir, I have tried this by setting -T1222 option as startup parameters in sql server service properties. But i am unable to get any log about deadlock in case of deadlock occurance.

i have also tried:

DBCC TRACEON(1222,-1);

exec sys.xp_readerrorlog in sql server but this also don't do anything for me.

sir, please tell me how to trace deadlocks.

Friday, September 13, 2013 - 6:23:39 AM - Rajas209 Back To Top

Thank god for this article. Ended up using SQL negotiator pro.

what a life saver thanks casey

Friday, August 30, 2013 - 2:33:26 AM - casey Back To Top

Great tips.

Though it is sometime very time consuming to figure out the actual cause of the deadlock and in a critical business environment as we all know time is money. I would suggest also looking at 3rd part software such as SQL Negotiator Pro at www.aphilen.com as this can easily monitor your database for current transactions, active jobs, current deadlocks, active connections by IP address. If a deadlock if found on an object then a visual representation of all dependencies can be viewed which will allow you to prioritize jobs.   

Tuesday, May 21, 2013 - 2:16:19 PM - Mark B Back To Top

BTW, the log file it logs to is something like C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\errorlog (will vary based on where sql is isntalled.)

Friday, May 17, 2013 - 7:16:14 AM - MMM Back To Top

Thank you! The most useful article

Wednesday, May 08, 2013 - 5:25:27 AM - Borota Igor Back To Top

Thank you very much, that was very helpfull, i applied it to live environment and find out that issue was i didn't have primary key on my table, so locking issues were everywhere!

Thursday, March 28, 2013 - 7:24:45 AM - dinesh Back To Top


What is global and session regarding Trace flag.




Wednesday, February 27, 2013 - 7:25:30 AM - Carlos Matos Back To Top

This article was waht i needed. Thanks.

Wednesday, November 28, 2012 - 9:16:02 PM - Bich Ly Back To Top

Your article is very useful for me. Thanks a lot.

Learn more about SQL Server tools