SQL Server: The list of Important Trace Flags

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing some of the important Trace Flag of the SQL Server.

Trace flags are frequently used to debug stored procedures and diagnose performance issues.

There are two types of Trace Flag, you can enable particular trace Globally and Session Specific.

You can also visit this article, How to enable and disable default trace of SQL Server.

Enable or Disable Trace Flag for Session:

Enable or Disable Trace Flag for Globally:

Example:

Trace Flag 1222:
Write the information about resources and types of locks in an XML format.

Trace Flag 1204:
Write the information about the deadlock in a text format.

Trace Flag 7806:
Enables a dedicated administrator connection on SQL Server Express Edition.

Trace Flag 1806:
You can disable the instant file initialization.

Trace Flag 4616:
The Application can access server level roles.

Trace Flag 3625:
It limits the information for those users who are not part of the sysadmin role and it prevents sensitive information.

Trace Flag 3608:
It stops the SQL Server to start automatically backup and restore for all Databases except the Master database.

Trace Flag 3226:
When we are taking log backups frequently, we can avoid some unnecessary additional log information.

Trace Flag 3014:
Trace more information of error log during the backup operation.

Trace Flag 3505:
It disables all information about the instant file initialization.