Trace flag

To enable a trace: DBCC Traceon (flag) or DBCC Traceon (flag,-1). The later one apply the trace to all connections whereas the first only apply to current connection.
To disable a trace: DBCC Traceoff (flag)
To check trace status: DBCC TraceStatus(-1)

Deadlock Information (1204)
This commonly used trace flag detects deadlocks and outputs the deadlock information. I'll cover much more detail about deadlocks and this trace flag in the next chapter.

Detailed Deadlock Information (1205)
This trace flag sends detailed information about the deadlock to the error log.

Disable Parallel Checking (2528)
You can use this trace flag to disable SQL Server from using any processor other than the primary processor when performing consistency checks (DBCCs). If you have a multiprocessor machine running a DBCC command, enabling this flag worsens performance considerably. (SQL Server uses multiple processors for running a DBCC command starting with SQL Server 2000.)

Network Database Files (1807)
SQL Server will not allow you to create a database or log file on a networked drive by default. If you attempt to do this, you receive error 5105, which states 'Device Activation Error.' The 1807 trace flag provides a workaround for this restriction, and you can create database files on a mapped drive or UNC path.
However, just because you can do something doesn't mean you should. This trace is undocumented for a good reason. It is an incredibly bad idea to place a database file on a network drive. The support for this feature was added to help vendors like Network Appliance ( http://www.netapp.com/). Storing data on a network drive opens another potential can of worms, because you must monitor and provide redundancy for that drive. If network connectivity is interrupted, your database goes down.

Send Trace Output to Client (3604)
Trace flag 3604 is the most commonly used trace flag. It sends the output of a trace to the client. For example, before you can run DBCC PAGE, which views data page information, you must run this trace flag.

Send Trace Output to Error Log (3605)
This trace is similar to trace flag 3604, but this flag sends the results to the error log.

Skip Automatic Recovery (3607)
Trace flag 3607 skips the recovery of databases on the startup of SQL Server and clears the TempDB. Setting this flag lets you get past certain crashes, but there is a chance that some data will be lost.

Skip Automatic Recovery Except Master (3608)
This trace is similar to 3607, but the TempDB in this case is not cleared and only the master database is recovered.

Log Record for Connections (4013)
This trace flag writes an entry to the SQL Server error log when a new connection is established. If you set this option, your error log can fill up quickly. For each connection that occurs, the trace flag writes two entries that look like this:

Login: sa saSQL Query Analyzer(local)ODBCmaster, server process ID (SPID): 57, kernel process ID (KPID): 57.
Login: sa XANADUsaSQL Query Analyzer(local)ODBCmaster, server process ID (SPID): 57, kernel process ID (KPID): 57.

Skip Startup Stored Procedures (4022)
This is a handy trace flag for troubleshooting. It forces SQL Server to skip startup stored procedures. This is especially useful if a stored procedure has been altered and causes harm to your system. After you set this trace flag, you can then debug the stored procedure and set it back to its original state.

Detailed Linked Server error message (7300)
The error messages that linked servers return are sometimes very generic. Turn on the 7300 trace flag if you want to receive more detailed information.

Ignore All Index Hints (8602)
Trace flag 8602 is a commonly used trace flag to ignore index hints that are specified in a query or stored procedure. This is a fantastic option when you're trying to determine if an index hint is hurting more than helping. Rather than rewriting the query, you can disable the hint using this trace flag and rerun the query to determine if SQL Server is handling the index selection better than the index hint.

Disable Locking Hints (8755)
Trace flag 8755 will disable any locking hints like READONLY. By setting this, you allow SQL Server to dynamically select the best locking hint for the query. If you feel the query's locking hint may be hurting performance, you can disable it and rerun the query.

Disable All Other Hints (8722)
Lastly, the 8722 trace flag will disable all other types of hints. This includes the OPTION clause.

Tip: By running all three 8602, 8755, and 8722 trace flags, you can disable all hints in a query. If you feel your performance is being negatively affected by a hint, you can set these rather than rewrite all the queries while you test. Generally speaking, there's no reason to place hints on queries in SQL Server 7.0 or 2000.