Some Important Trace Flags for SQL Server DBA
In this post we will discuss Some Important Trace Flags for SQL Server DBA. A trace flag is used to set specific server characteristics or to switch off a particular behavior of the SQL Server. You can enable the trace flag from Startup Parameters, Session and Global Session level.
| Trace Flag | Description |
| Trace Flag 610 | Minimally logged DML operations into Indexed tables, transaction log and allow bulk loading |
| Trace Flag 4199 | Enable query optimizer fixes |
| Trace Flag 1222 | Writes the deadlock information in XML format into error log |
| Trace Flag 835 | Enable the Lock Pages in memory for standard edition |
| Trace Flag 1118 | Directs SQL Server to allocate full extents to each tempDB objects |
| Trace Flag 1204 | Write information about the deadlock in text format |
| Trace Flag 1211 | Disable the lock escalation based on the memory pressure |
| Trace Flag 3226 | Prevents successful backup operation being logged |
| Trace Flag 3014 | Returns more information to error log about backup operation |
| Trace Flag 3502 | Write information about the checkpoint in the error log |
| Trace Flag 3505 | Disables automatic checkpoint |
| Trace Flag 3004 | Returns more information about the instant file initialization |
| Trace Flag 1806 | Disables the instant file initialization |