Flag Description

200 Displays messages about the before image of the query-tree.

201 Displays messages about the after image of the query-tree.

241 Compress all query-trees whenever the SQL dataserver is started.

260 Reduce TDS (Tabular Data Stream) overhead in stored procedures. Turn off done-in-proc packets. Do not use this if your application is a ct-lib based application; it'll break.

Why set this on? Glad you asked, typically with a db-lib application a packet is sent back to the client for each batch executed within a stored procedure. This can be taxing in a WAN/LAN environment.

299 This trace flag instructs the dataserver to not recompile a child stored procedure that inherits a temp table from a parent procedure.

302 Print information about the optimizer's index selection.

303 Display OR strategy

304 Revert special or optimizer strategy to that strategy used in pre-System 11 (this traceflag resolved several bug issues in System 11, most of these bugs are fixed in SQL Server 11.0.3.2)

310 Print information about the optimizer's join selection.

311 Display the expected IO to satisfy a query. Like statistics IO without actually executing.

317 Provide extra optimization information.

319 Reformatting strategies.

320 Turn off the join order heuristic.

324 Turn off the like optimization for ad-hoc queries using @local_variables.

602 Prints out diagnostic information for deadlock prevention.

603 Prints out diagnostic information when avoiding deadlock.

699 Turn off transaction logging for the entire SQL dataserver.

1204* Send deadlock detection to the errorlog.

1205 Stack trace on deadlock.

1206 Disable lock promotion.

1603* Use standard disk I/O (i.e. turn off asynchronous I/O).

1605 Start secondary engines by hand

1606 Create a debug engine start file. This allows you to start up a debug engine which can access the server's shared memory for running diagnostics. I'm not sure how useful this is in a production environment as the debugger often brings down the server. I'm not sure if Sybase have ported the debug stuff to 10/11. Like most of their debug tools it started off quite strongly but was never developed.

1608 Startup only engine 0; use dbcc engine(online) to incrementally bring up additional engines until the maximum number of configured engines.

1610* Boot the SQL dataserver with TCP_NODELAY enabled.

1611* If possible, pin shared memory -- check errorlog for success/failure.

1613 Set affinity of the SQL dataserver engine's onto particular CPUs -- usually pins engine 0 to processor 0, engine 1 to processor 1...

1615 SGI only: turn on recoverability to filesystem devices.

2512 Prevent dbcc from checking syslogs. Useful when you are constantly getting spurious allocation errors.

3300 Display each log record that is being processed during recovery. You may wish to redirect stdout because it can be a lot of information.

3500 Disable checkpointing.

3502 Track checkpointing of databases in errorlog.

3601 Stack trace when error raised.

3604 Send dbcc output to screen.

3605 Send dbcc output to errorlog.

3607 Do not recover any database, clear tempdb, or start up checkpoint process.

3608 Recover master only. Do not clear tempdb or start up checkpoint process.

3609 Recover all databases. Do not clear tempdb or start up checkpoint process.

3610 Pre-System 10 behavior: divide by zero to result in NULL instead of error - also see Q7.5.

3620 Do not kill infected processes.

4012 Don't spawn chkptproc.

4013 Place a record in the errorlog for each login to the dataserver.

4020 Boot without recover.

5101 Forces all I/O requests to go thru engine 0. This removes the contention between processors but could create a bottleneck if engine 0 becomes busy with non-I/O tasks. For more information...5101/5102.

5102 Prevents engine 0 from running any non-affinitied tasks. For more information...5101/5102.

7103 Disable table lock promotion for text columns.

8203 Display statement and transaction locks on a deadlock error.