Finding SQL Server Deadlocks Using Trace Flag 1222

最新推荐文章于 2018-02-22 20:19:10 发布

转载最新推荐文章于 2018-02-22 20:19:10 发布 · 430 阅读

SQL Server Administration 专栏收录该内容

14 篇文章

订阅专栏

本文介绍如何使用 SQL Server 的全局跟踪标志 1222 来记录死锁信息，并通过错误日志来分析引起死锁的具体查询。此外，还介绍了设置跟踪标志的方法及其状态检查。

Problem

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?

Solution

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