Important Trace Flags for 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.

DBCC TraceON(Flag Number) – at session level
DBCC TraceON(Flag Number, -1) -- Global

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

1 thought on “Important Trace Flags for DBA

Leave a Reply to Rose K Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s