SQLArchive: Trace Flags and How To Turn Them On Globally and Locally

I always used to get confused with turning trace flags Globally and Locally, So I end up writing this post to have a reference any time in future.

There are two different ways of turning them on.

Enabling\Disabling the Trace Flags using the DBCC command.
Enabling the Trace Flags using the startup parameters.

Enabling\Disabling the Trace Flags using the DBCC command: Using the DBCC commands we can turn on trace flags both globally and locally.

Syntax: DBCC TRACEON (3604) -- will turn on Trace 3604 Locally

DBCC TRACEON (3604,-1) -- will turn on Trace 3604 Globally

DBCC TRACEOFF (3604) -- will turn off Trace 3604 Locally

DBCC TRACEOFF (3604, -1) -- will turn off Trace 3604 Globally

Note: The trace flags enabled with DBCC command will be no longer available after the SQL Server restarts. If you want to make the trace available even after server reboot or SQL Server service restart we need to use second method.

Enabling the Trace Flags using the startup parameters: All the traces that enabled with this method are Global traces. And this method needs a restart of the SQL Server services to make the trace effective.

Steps to fallow: Open SQL Server Configuration Manager and right click on SQL Server Service and click on properties to edit the properties to add Startup Parameter -T3604 then click Add, Apply and OK and restart the SQL Server services, this will enable the trace 3604.

Note: We use this same method to add the other startup parameters like (-m\-f etc).

We still can turn off traces turned on with second method using the DBCC Command. As they are global traces you have turn them off globally. And they will be turned on automatically when the SQL Serveries are restarted as they are startup parameters.

DBCC TRACEOFF (3604,-1)

List all the currently Enabled\Running Trace Flags:

We can use DBCC command to get a list of all the running trace flags.

Syntax: DBCC TRACESTATUS(-1)