Categories
Sql Server

Some Important Trace Flags for SQL Server DBA

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