Trace Flags

Trace flags give you advanced mechanisms to tap into hidden SQL Server features and troubleshooting tactics. In some cases, they allow you to override the recommended behavior of SQL Server to turn on features like network-drive support for database files. In other cases, trace flags can be used to turn on additional monitoring as some flags that help diagnose deadlocks. To turn on a trace flag, use the DBCC TRACEON command, followed by the trace you'd like to turn on, as shown below:

 DBCC TRACEON (1807) 

To turn off the trace, use the DBCC TRACEOFF command. This command is followed by which traces you'd like to turn off (multiple traces can be separated by commas) as shown here:

 DBCC TRACEOFF (1807, 3604) 

When you turn on a trace, you are turning it on for a single connection by default. For example, if you turn on trace flag 1807, which helps diagnose deadlocks, you will only be able to diagnose deadlocks in the scope of the connection that issued the DBCC TRACEON command. You can also turn on the trace at a server level by issuing the command followed by the -1 switch:

 DBCC TRACEON (1807, -1) 

Once you've turned on the traces, the next thing you're probably going to want to do is determine whether the trace is actually running. To do this, you can issue the DBCC TRACESTATUS command. One method to issue the command is to interrogate if a given trace is running:

 DBCC TRACESTATUS (3604) 

This command would return the following results if the trace is not turned on:

 TraceFlag Status Global Session --------- ------ ------ ------- 3604      0      0      0 (1 row(s) affected) 

If you wish to see all traces that apply to the connection, run the command with the -1 parameter, as shown below:

 DBCC TRACESTATUS (-1) 

As you can see in the following results of this query, two traces are turned on. Trace flag 1807 is turned on globally for every connection into the SQL Server, and trace flag 3604 is turned on for this session:

 TraceFlag Status Global Session --------- ------ ------ ------- 1807      1      1      0 3604      1      0      1 (2 row(s) affected) 

If no traces are turned on, you would receive the following message:

 DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

Your instance of SQL Server should not have trace flags turned on indefinitely, unless you have support from Microsoft to do so. Trace flags may cause your instance to behave abnormally when left to run all the time. The flag you use today may also not be available in a future release or service pack of SQL Server. If you are in debug mode, you can turn on a trace flag from the command prompt when starting SQL Server. As mentioned earlier in this chapter, you can also start a trace when SQL Server starts up at the command prompt by calling the sqlservr.exe program and passing the -T switch after it.

To date, we've mentioned a few trace flags in passing but nothing in much more detail. As you proceed through this book, you'll see a number of other trace flags in practice. A favorite trace flag in your DBA toolbox, though, is certainly going to be the deadlock traces.

A deadlock is encountered when one or more resources try to access an item being permanently locked by another resource. The requesting connection would then be terminated, and a deadlock error would appear to the client. We cover much more about what deadlocks are and how they can affect your instance's performance in Chapters 6 and 7, but for the time being, note that if you turn on trace flag 1204, anytime a deadlock is encountered, a message the resembles the following message will be sent to the SQL Server error log:

 Deadlock encountered .... Printing deadlock information Wait-for graph       Node:1       RID: 8:1:140:0                 CleanCnt:3 Mode:X Flags: 0x2        Grant List 1:          Owner:0x03364B20 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x059D67D4          SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 10          Input Buf: Language Event: exec p2        Requested By:          ResType:LockOwner Stype:'OR'Xdes:0x059D6248 Mode: U SPID:57 BatchID:0 ECID:0 TaskProxy:(0x04AEE33C) Value:0x335cfe0 Cost:(0/216)       Node:2       RID: 8:1:138:0                 CleanCnt:2 Mode:X Flags: 0x2        Grant List 1:          Owner:0x03364620 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:57 ECID:0 XactLockInfo: 0x059D626C          SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 10          Input Buf: Language Event: exec p1        Requested By:          ResType:LockOwner Stype:'OR'Xdes:0x059D67B0 Mode: U SPID:59 BatchID:0 ECID:0 TaskProxy:(0x05FD433C) Value:0x33645e0 Cost:(0/116)       Victim Resource Owner:        ResType:LockOwner Stype:'OR'Xdes:0x059D67B0 Mode: U SPID:59 BatchID:0 ECID:0 TaskProxy:(0x05FD433C) Value:0x33645e0 Cost:(0/116) 

This should tell you that you have a serious problem with a query or series of queries. It outputs the SPID (also referred to as process ID from earlier sections) that blocked the other process (called the victim). In this case, the victim was SPID 59, the results of that query were lost, and you'd have to rerun the query.

Trace flag 1222 returns much more information than the 1204 trace flag to the error log, and it resembles an XML document (although it does not comply with an XSD). The results are very verbose, and that is why you typically only see this enabled as a secondary step if the 1204 results were inadequate for you to solve the problem. The results of the trace flag's output would give you the queries that were the victim and blocking queries and would resemble something like the following output from an error log:

       deadlock-list       deadlock victim=process279b098        process-list         process id=process279b098 priority=0 logused=116 waitresource=RID: 7:1:141:0 waittime=10000 ownerId=22637 transactionname=user_transaction lasttranstarted=2005-01-25T16:39:22.050 XDES=0x529e7e0 lockMode=U schedulerid=2 kpid=2120 status=suspended spid=55 sbid=0 ecid=0 transcount=2 lastbatchstarted=2005-01-25T16:39:35.357 lastbatchcompleted=2005-01-25T16:36:55.217 clientapp=SQL Server Management Studio - Query hostname=USER13 hostpid=2852 loginname=DOMAIN\user1 isolationlevel=read committed (2) xactid=236223201313 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200          executionStack           frame procname=general.dbo.p2 line=10 stmtstart=186 stmtend=228 sqlhandle=0x03000700e8ea0d065c0a1101e79500000100000000000000      UPDATE table1 SET column1=5           frame procname=adhoc line=1 sqlhandle=0x0100070068747519405a6003000000000000000000000000      exec p2          inputbuf      exec p2         process id=process279b168 priority=0 logused=216 waitresource=RID: 7:1:144:0 waittime=2453 ownerId=22635 transactionname=user_transaction lasttranstarted=2005-01-25T16:39:19.190 XDES=0x529e258 lockMode=U schedulerid=2 kpid=164 status=suspended spid=53 sbid=0 ecid=0 transcount=2 lastbatchstarted=2005- 01-25T16:39:32.503 lastbatchcompleted=2005-01-25T16:39:08.627 clientapp=SQL Server Management Studio - Query hostname=USER13 hostpid=3192 loginname=DOMAIN\user1 isolationlevel=read committed (2) xactid=227633266690 currentdb=7 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200          executionStack           frame procname=general.dbo.p1 line=10 stmtstart=182 stmtend=224 sqlhandle=0x03000700afc619055c0a1101e79500000100000000000000      UPDATE table1 SET column1=2           frame procname=adhoc line=2 stmtstart=6 sqlhandle=0x01000700d6e53430205b6003000000000000000000000000      exec p1          inputbuf      exec p1        resource-list         ridlock fileid=1 pageid=144 dbid=7 objectname=general.dbo.t2 id=lock368ab80 mode=X associatedObjectId=72057594038517760          owner-list           owner id=process279b098 mode=X          waiter-list           waiter id=process279b168 mode=U requestType=wait         ridlock fileid=1 pageid=141 dbid=7 objectname=general.dbo.t1 id=lock368ac80 mode=X associatedObjectId=72057594038452224          owner-list           owner id=process279b168 mode=X          waiter-list           waiter id=process279b098 mode=U requestType=wait 

The output will also give you other verbose information that can be used to debug your instance, such as the user name of the victim or deadlocking process and other connection information.