dbcc Trace

//z 2012-5-11 14:04:12 PM IS2120

DBCC TRACEON - Enable trace flags.
DBCC TRACEOFF - Disable trace flags.
DBCC TRACESTATUS - Display the status of trace flags.

Syntax DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [WITH NO_INFOMSGS] DBCC TRACEOFF ( trace# [ ,...n ] [ , -1 ] ) [WITH NO_INFOMSGS] DBCC TRACESTATUS ( [ [trace# [,...n ] ] [,] [-1] ] ) [WITH NO_INFOMSGS] Key: trace# - Number of the trace flag(s) -1 - Display the status of trace flags that are enabled globally. NO_INFOMSGS - Suppress all information messages.

By default all trace flags that are enabled for the session are displayed.

Examples

-- Turn flag on DBCC TRACEON (3205) GO -- Turn flag on globally DBCC TRACEON (2528, -1) GO -- Turn flag off DBCC TRACEOFF (3205); GO -- Show flag status DBCC TRACESTATUS (2528, 3205) GO

//z 2012-5-11 14:04:12 PM IS2120

Trace Flags

#	Flag
260	Print versioning information about extended stored procedure DLLs.	global or session
1204	Returns the resources and types of locks participating in a deadlock and also the current command affected.	global
1211	Disable all lock escalation. This trace flag may reduce performance. Takes precedence over flag 1224	global or session
1222	Return the resources,command and lock types that are participating in a deadlock (XML).	global
1224	Disable lock escalation based on the number of locks. Helps avoid "out-of-locks" errors when many locks are being used.	global
4616	Makes server-level metadata visible to application roles. i.e Revert to pre- SQL Server 2005 behavior.	global
2528	Disable parallel checking of objects by DBCC CHECK commands. Parallel DBCC should typically be left enabled.	global or session
3205	Disable hardware compression for tape drivers.	global or session
3625	Limit the amount of information returned in error messages.	global
7806	Enable a dedicated administrator connection (DAC) on SQL Server Express.	global

In order to see things printed to console, users would need to start up SQL Server from a command window with a -c argument. However, this is not what customer usually does. SQL Server provides another  traceflag -T3605 which can be used to print console data to SQL Server errorlog file.

In this blog, I will introduce two useful traceflags related to TDS, -T4052/-T4055.-T4052 can be used to print the TDS packet sent to client (i.e. output) to console.-T4055 can be used to print the TDS packet received (i.e. input) to console.Both traceflag are startup only, meaning it can only be turned on during server startup. With these two traceflags, you can see everything that flows in and out of SQL Server.

In order to see things printed to console, users would need to start up SQL Server from a command window with a -c argument. However, this is not what customer usually does. SQL Server provides another  traceflag -T3605 which can be used to print console data to SQL Server errorlog file.

You can search master.mdf HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server (remove Wow6432Node if 64-bit instance) to find the location for configuring startup parameters in SQL Server. The location is under:

HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\<InstanceID>\MSSQLServer\Parameters

Add three string values under the key:

SQLArg3  --> -T4052
SQLArg4  --> -T4055
SQLArg5  --> -T3605

The number after SQLArg does not have to be 3/4/5. As long as it's unique, it's OK. Remember to update <InstanceID> with the instance ID of your own instance.

Now, restart your SQL Server, you will see all TDS data in SQL Server errorlog.

If you are using SQL Server 6.0, set trace flag 4032 to capture incoming SQL commands to the server. The following are two basic methods you can use to do this:

• Start Sqlservr.exe with the command line parameters -T4032and -T3605.
• Run dbcc traceon(-1, 3605, 4032) from Isqlw.exe or Isql.exeto set up capturing of SQL commands.

//z 2012-5-11 14:04:12 PM IS2120@

Trace flags can be used to alter SQL Server behavior temporarily. Trace flags can be turned on for a specific connection, or server-wide. Trace flags can be a great tool for troubleshooting a particular issue, however be forewarned: some trace flags are dangerous -

use them at your own risk and be prepared to rebuild the server from scratch. Furthermore, functionality of certain trace flags might not be supported in future versions of SQL Server, so definitely avoid setting trace flags in your T-SQL code. We

strongly recommend against using undocumented trace flags on production systems unless you're directed to do so by Microsoft's technical support.

To turn on a particular trace flag for a given connection, you execute the DBCC TRACEON command with the trace flag number specified in parenthesis, as follows:

1. /* send the output of DBCC commands to the client */ DBCC TRACEON (3604)

To enable a trace flag on the SQL Server instance level you need to start SQL Server with the /T parameter followed by the trace flag number. If you start SQL Server with a trace flag, all connections will automatically have the same trace flag turned on. For example, to collect deadlock related information you could start the default instance of the SQL Server instance from the command prompt as follows:

1. NETSTART MSSQLSERVER /T1205

You can check the status of a particular trace flag by executing DBCC TRACESTATUS. Turn off a previously turned on trace flag using DBCC TRACEOFF. For example:

1. DBCCTRACESTATUS(3604) DBCC TRACEOFF(3604)

You can check all trace flags turned on for the current connection by passing -1 as the parameter of DBCC TRACESTATUS, for example:

1. DBCCTRACESTATUS(-1)

Results:

1. TraceFlag  Status  --------- ------  2520       1  3604       1

If you have no trace flags turned on, SQL Server will return the following message:

1. Traceoption(s)not enabled forthis connection.Use 'DBCC TRACEON()'.

Next, we discuss some of the more frequently used trace flags. As mentioned above, some of these can be very powerful and therefore should be handled with care.

DBCC TRACEON / TRACEOFF (-1)

This flag advises SQL Server to turn on the trace flags turned on for the current connection on all subsequent client connections. For example, if you have turned on 3604 and 2520 on the current connection and you execute DBCC TRACEON(-1) all subsequent connections to the server will have 3604 and 2520 turned on. Similarly, if you wish to turn a particular trace flag for all connections simply execute it along with -1, as in DBCC TRACEON(-1, 3604). Executing DBCC TRACEOFF(-1) will automatically turn off all trace flags on the current and any subsequent connections.

DBCC TRACEON (2528)

This flag disables parallelism during executing of maintenance DBCC statements, such as DBCC CHECKDB, DBCC CHECKFILEGROUP and DBCC CHECKTABLE. By default SQL Server will determine the needed degree of parallelism during query execution. Usually it is recommended to let SQL Server decide whether parallelism will be useful. Occasionally, if you only wish to use a single processor for DBCC statements, you might wish to override the default behavior. Remember that turning off parallelism might increase the total time required for executing DBCC commands.

DBCC TRACEON(3604) and DBCC TRACEON(3605)

The first flag (3604) sends the output of (some) DBCC commands and trace flags to the Query Analyzer; 3605 sends the same output to SQL Server error log. For example, the following commands will generate a list of the 10 longest buffer chains in Query Analyzer:

1. DBCCTRACEON(3604) DBCC BUFCOUNT

Results:

1. ****THE 10 LONGEST BUFFER CHAINS ****      bucket number = 514     chain size =3     bucket number = 522     chain size =2     bucket number = 770     chain size =2     bucket number = 1026    chain size =2     bucket number = 269     chain size =1     bucket number = 272     chain size =1     bucket number = 274     chain size =1     bucket number = 281     chain size =1     bucket number = 283     chain size =1     bucket number = 284     chain size =1    The Smallest Chain Sizeis: 0    The Average Chain Size is: 0.005066

If you turn off 3604 and turn on 3605, instead you'll get the same result in the error log. You can double check this by executing the following:

1. EXECmaster..xp_readerrorlog

Abbreviated results:

1. ERRORLOG                                                                        ContinuationRow  2003-10-24 21:00:31.51 spid51       bucket number = 514       chain size =3    0  2003-10-24 21:00:31.51 spid51       bucket number= 522       chain size= 2    0  2003-10-24 21:00:31.51 spid51       bucket number= 770       chain size= 2    0  2003-10-24 21:00:31.51 spid51       bucket number= 1026      chain size= 2    0  2003-10-24 21:00:31.51 spid51       bucket number= 269       chain size= 1    0  2003-10-24 21:00:31.51 spid51       bucket number= 272       chain size= 1    0  2003-10-24 21:00:31.51 spid51       bucket number= 274       chain size= 1    0  2003-10-24 21:00:31.51 spid51       bucket number= 281       chain size= 1    0  2003-10-24 21:00:31.51 spid51       bucket number= 283       chain size= 1    0  2003-10-24 21:00:31.51 spid51       bucket number= 284       chain size= 1    0  2003-10-24 21:00:31.51 spid51      The Smallest ChainSize is: 0                0  2003-10-24 21:00:31.51 spid51      The Average Chain Size is: 0.005066          0

DBCC TRACEON(1204) , DBCC TRACEON(1205) and DBCC TRACEON(1206)

These trace flags are used to troubleshoot deadlocks. 1204 returns deadlock chains and the victim SPID. 1205 returns the details of the commands (stack traces) involved in the deadlock. Along with these flags you should also turn on 3605 to send the output of the trace to the SQL Server error log. 1206 can be used to supplement the information collected by the other two trace flags by returning all lock activities performed by deadlocked connections. The output of 1206 can be very large.

If you wish to see what deadlock output looks like, simply open two connections to the same SQL Server instance through Query Analyzer and execute following on one of them:

1. DBCCTRACEON(-1, 1204) DBCC TRACEON(-1, 1205) DBCC TRACEON(-1, 3605)

Next execute the following on the first connection:

1. USEpubs  BEGIN TRAN  UPDATE titles   SET title ='deadlock battle'    WAITFORDELAY '00:00:05'    UPDATE authors   SETaddress = '110 north main'

At the same time execute the following script from the other connection:

1. USEpubs  BEGIN TRANSACTION  UPDATE authors  SET address ='115 East 43rd street'    UPDATEtitles   SET title = 'who can win?'

One of these connections will be chosen as a deadlock victim and its transaction will be aborted by SQL Server. Now if you read the error log, you will find entries similar to the following:

1. spid4    ----------------------------------     spid4     Starting deadlock search1     spid4     Target Resource Owner:       spid4      ResType:LockOwner Stype:'OR'Mode: U SPID:53 ECID:  Ec:(x195 756 )               Value: x1916eb4     spid4      Node:1  ResType:LockOwner Stype:'OR'Mode: U SPID:53 ECID:  Ec:(x195 756 )               Value: x1916eb4     spid4     spid4     Enddeadlock search 1 ... a deadlock was not found.      spid4     ----------------------------------     spid4     ----------------------------------   spid4     Starting deadlock search2      spid4     Target Resource Owner:      spid4      ResType:LockOwner Stype:'OR'Mode: U SPID:52 ECID:  Ec:(x1947756 )                Value: x1916e72     spid4      Node:1  ResType:LockOwner Stype:'OR'Mode: U SPID:52 ECID:  Ec:(x1947756 )                Value: x1916e72     spid4      Node:2  ResType:LockOwner Stype:'OR'Mode: U SPID:53 ECID:  Ec:(x195 756 )                Value: x1916eb4     spid4      Cycle:  ResType:LockOwner Stype:'OR'Mode: U SPID:52 ECID:  Ec:(x1947756 )                Value: x1916e72      spid4       spid4       spid4    <b>Deadlock cyclewas encountered .... verifying cycle</b>     spid4      Node:1  ResType:LockOwner Stype:'OR'Mode: U SPID:52 ECID:  Ec:(x1947756 )                Value: x1916e72  Cost:(/16  )   spid4      Node:2  ResType:LockOwner Stype:'OR'Mode: U SPID:53 ECID:  Ec:(x195 756 )                Value: x1916eb4  Cost:(/B88)    spid4      Cycle:  ResType:LockOwner Stype:'OR'Mode: U SPID:52 ECID:  Ec:(x1947756 )                Value: x1916e72  Cost:(/16  )    spid4     spid4   <b>Deadlock encountered .... Printing deadlock information</b>     spid4     spid4     Wait-for graph     spid4     spid4     Node:1    spid4     KEY: 5:1977 58 79:1( 1 1aedb232b)CleanCnt:1 Mode: X Flags:  x    spid4      GrantList::    spid4        Owner: x1916ee2  Mode: X        Flg: x Ref:  Life: 2       SPID:53 ECID:    spid4        SPID: 53 ECID:  Statement Type: UPDATELine #: 1    spid4        InputBuf: Language Event: <b>begintran   update authors set address = '115 East 43rd street '  update titles settitle = 'who can win?'</b>     spid4      RequestedBy:     spid4        ResType:LockOwner Stype:'OR'Mode: U SPID:52 ECID:  Ec:(x1947756 )                 Value: x1916e72  Cost:(/16  )   spid4       spid4     Node:2     spid4     KEY: 5:2121 58592:1(a7  64fb1eac)CleanCnt:1 Mode: X Flags:  x     spid4      GrantList::     spid4        Owner: x191813   Mode: X        Flg: x Ref:  Life: 2       SPID:52 ECID:    spid4        SPID: 52 ECID:  Statement Type: UPDATELine #: 1    spid4        InputBuf: Language Event: <b>begintran   update titles set title=' deadlock battle''    waitfor delay '  :  : 5'  update authors set address='110 north main '</b>     spid4      Requested By:     spid4        ResType:LockOwner Stype:'OR' Mode: U SPID:53 ECID:  Ec:( x195 756 )                  Value: x1916eb4  Cost:( /B88)    spid4     Victim Resource Owner:     spid4      ResType:LockOwner Stype:'OR' Mode: U SPID:53 ECID:  Ec:( x195 756 )                Value: x1916eb4  Cost:( /B88)    spid4     spid4     <b>End deadlock search 2 ... a deadlock was found.</b>     spid4     ----------------------------------     spid4     ----------------------------------     spid4     Starting deadlock search 3    spid4     Target Resource Owner:     spid4      ResType:LockOwner Stype:'OR' Mode: U SPID:53 ECID:  Ec:( x195 756 )                Value: x1916eb4     spid4      Node:1  ResType:LockOwner Stype:'OR' Mode: U SPID:53 ECID:  Ec:( x195 756 )                Value: x1916eb4     spid4      Node:2  ResType:LockOwner Stype:'OR' Mode: U SPID:52 ECID:  Ec:(x1947756 )                Value: x1916e72     spid4     spid4     <b>Previous victim encountered ... abortingsearch</b>          spid4     spid4    End deadlock search3 ... a deadlock was not found.        spid4     ----------------------------------

If you had also turned on trace flag 1206 you would get numerous messages similar to the following in the error log:

1. Process 52 acquiring IX lockon PAG: 5:1:99 (classbit2000000 ref1)   result: OK  Process 52 acquiring X lock onKEY: 5:2121058592:1 (b60057ff7752)(class bit2000000 ref1)   result: OK  Process 52 releasing lock referenceon KEY: 5:2121058592:2(9002e988d824) Process 5 releasing all locks @19116B3C

DBCC TRACEON(3205)

This flag disables hardware compression for tape drives. If the tape drive supports the compression, the BACKUP statement that backs up the database directly to tape will take advantage of hardware compression. If you must exchange tapes with another office where hardware compression is not supported, you might wish to turn on trace flag 3205 so that your tapes are compatible with the other office's hardware.

DBCC TRACEON (4013)

This flag can be used to audit connections to the server. When turned on, the SQL Server error log will contain an entry for each successful connection. The log entry will look similar to the following:

1. Login: johndoe BP-5CHSFFH2HEJ1johndoejohndoeSQL Query AnalyzerBP-5CHSFFH2HEJ1ODBCmaster,   server process ID(SPID): 55, kernel process ID(KPID): 55.

As you might imagine the error log on a busy server will grow quite voluminous if this flag is turned on.

DBCC TRACEON(4022)

This flag is used to bypass procedures marked for automatic execution at startup. Note that automatically executed procedures are also skipped if SQL Server is started with minimal configuration.

DBCC TRACEON(2520)

This flag can be used to force DBCC HELP to return syntax of undocumented DBCC statements. If 2520 is not turned on, DBCC HELP will refuse to give you the syntax stating: "No help available for DBCC statement 'undocumented statement'".

DBCC TRACEON(2588)

This flag can be used to force DBCC HELP to return syntax of undocumented DBCC statements in SQL Server 2005 and 2008.

1. DBCCTRACEON (2588);DBCCHELP ('?')GO DBCC TRACEOFF (2588);

DBCC TRACEON(1200)

This flag can be used to get a detailed report of all locks acquired by each SQL statement on the current connection - the output can be large depending on the number of rows involved. For example, take a look at the output of the following simple query:

1. SELECTa.au_id, b.royaltyper  FROMauthors a INNER JOIN titleauthor b  ONa.au_id = b.au_id  ORDER BY2

Results (abbreviated):

1. Process 54 acquiringIS lock onTAB: 5:53575229 [] (classbit0 ref1) result: OK  Process 54 acquiring ISlock on TAB: 5:1977058079 [] (classbit0 ref1) result: OK  Process 54 acquiring ISlock on PAG: 5:1:148 (classbit0 ref1) result: OK  Process 54 acquiring ISlock on PAG: 5:1:102 (classbit0 ref1) result: OK  Process 54 releasing lock onPAG: 5:1:102  Process 54 acquiring ISlock on PAG: 5:1:102 (classbit0 ref1) result: OK  Process 54 releasing lock onPAG: 5:1:102  Process 54 acquiring ISlock on PAG: 5:1:102 (classbit0 ref1) result: OK  Process 54 releasing lock onPAG: 5:1:102  Process 54 acquiring ISlock on PAG: 5:1:102 (classbit0 ref1) result: OK  Process 54 releasing lock onPAG: 5:1:102  Process 54 acquiring ISlock on PAG: 5:1:102 (classbit0 ref1) result: OK  Process 54 releasing lock onPAG: 5:1:102  Process 54 acquiring ISlock on PAG: 5:1:102 (classbit0 ref1) result: OK  Process 54 releasing lock onPAG: 5:1:102  Process 54 acquiring ISlock on PAG: 5:1:102 (classbit0 ref1) result: OK  Process 54 releasing lock onPAG: 5:1:102  Process 54 acquiring ISlock on PAG: 5:1:102 (classbit0 ref1) result: OK  Process 54 releasing lock onPAG: 5:1:102  Process 54 acquiring ISlock on PAG: 5:1:102 (classbit0 ref1) result: OK  Process 54 releasing lock onPAG: 5:1:102  …

DBCC TRACEON (1807)

This flag can be used to allow the creation of database files on a network share. You must specify a valid UNC path to the share in order to create database and log files.

1. DBCCTRACEON(3607),DBCC TRACEON(3608)and DBCC TRACEON(3609)

These flags can be used to skip the recovery process during SQL Server startup. 3607 does not recover any databases, 3608 recovers the master only. In addition, both trace flags cause SQL Server to skip stored procedures marked for automatic execution. Trace flag 3609 skips creation and clearing of tempdb at startup.

Warning: do

NOT use trace flags 3607-3609 unless you are directed to do so by Microsoft support professional.

DBCC TRACEON(4030), DBCC TRACEON(4031) and DBCC TRACEON(4032)

Flags 4030 and 4031 can be used to write the SQL statements submitted and output returned to the connections. Their functionality is similar to DBCC INPUTBUFFER and DBCC OUTPUTBUFFER respectively. The difference is that trace flags record all information in the error log for all connections. For example, we can set both of these flags globally and send the output to the error log with the following:

1. DBCCTRACEON(-1, 4031) DBCC TRACEON(-1, 4030) DBCC TRACEON(-1, 3605)

//z 2012-5-11 14:04:12 PM IS2120@

Next, execute a simple statement such as:

1. SELECTTOP 1 *FROM authors

You will see entries similar to the following in the error log:

1. 2003-10-24 23:09:08. spid52     Printing receive buffer:  01 01 00 42 00 00 01 00 53 00 45 00 4C 00 45 00   ...B....S.E.L.E.  43 00 54 00 20 00 54 00 4F 00 50 00 20 00 31 00   C.T. .T.O.P. .1.  20 00 2A 00 20 00 46 00 52 00 4F 00 4D 00 20 00    .*. .F.R.O.M. .    2003-10-24 23:09:08.78 spid52    61 00 75 00 74 00 68 00 6F 00 72 00 73 00 0D 00   a.u.t.h.o.r.s...      2003-10-24 23:09:08. spid52     Printing send buffer:     04 01 01 41 00 34 01 00 81 09 00 01 01 08 00 A7   ...A.4..........  0B 00 09 04 D0 00 34 05 61 00 75 00 5F 00 69 00   ......4.a.u._.i.  64 00 00 00 08 00 A7 28 00 09 04 D0 00 34 08 61   d......(.....4.a  00 75 00 5F 00 6C 00 6E 00 61 00 6D 00 65 00 00   .u._.l.n.a.m.e..  00 08 00 A7 14 00 09 04 D0 00 34 08 61 00 75 00   ..........4.a.u.  5F 00 66 00 6E 00 61 00 6D 00 65 00 00 00 08 00   _.f.n.a.m.e.....  AF 0C 00 09 04 D0 00 34 05 70 00 68 00 6F 00 6E   .......4.p.h.o.n  00 65 00 00 00 09 00 A7 28 00 09 04 D0 00 34 07   .e......(.....4.

Flag 4032 can be used to record every SQL statement executed against the server in the error log. The log entries will look similar to the following:

1. Text:use[pubs]  ODS Event: LanguageExec  Text:SELECTTOP 1 *FROM authors

DBCC TRACEON(8202)//z 2012-5-11 14:04:12 PM IS2120@CSDN

Used for replication, this flag forces SQL Server to replicate UPDATE statements as DELETE followed by an INSERT. This behavior could be useful if you wish to perform custom processing in replication stored procedures. For instance, you could write a record to the audit table every time record is updated through replication.

//z 2012-5-11 14:04:12 PM IS2120@