SQL Trace Flags

Trace flags that I’ve been made aware off … documented and undocumented

TF 834: kb920093: enable sql server to use large pages to help with performance
TF 1222: new deadlocks in a xml format
TF 3226: prevent successful backup msg to the errorlog
TF 4199: KB974006
TF 806: KB841776
TF 1200: returns locking information in real-time as your query executes
TF 1806: disables instant file initialization
TF 3014: returns more info to the errorlog about db backup/restore
TF 3502: writes info about checkpoints to the errorlog
TF 2371: change the way autostat work.  instead of waiting for 20% + 500 rows.
TF 610: enable minimal logging.  only for new pages; will still fully log for old pages
TF 1117: to ensure that your freespace is the same across all data files (a bit more info here. See Paul’s note section) – thanks Henrik Staun Poulsen.
TF 1118: forces uniform extent allocations instead of mixed page allocations.   The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points (more info here).

  • Henrik Staun Poulsen

    TF 1117: to ensure that your freespace is the same across all data files

    • red8rain

      Thanks Henrik, I will add that to my list, along with some info about it’s usage.