How To Set Trace Flag In Sql Server

What are trace flags

Trace flags are provided to change several default SQL Server behaviors. Each flag is predefined and can change a specific SQL Server behavior. Flag can be turned ON and OFF. The behaviors controlled past trace flags autumn in a vast scope. These may be related to locks, deadlocks, DML aspects, storage, logs, executions, information and log files, file groups, partitioning, HA, DR, parallelism etc. You should have clear information about furnishings of whatsoever trace flag before you set it ON. Hither is listing of trace flag numbers and their behavioral summary.

Nosotros tin use following command to go list of traces enabled on a server if whatever.

-- Get trace flags that are Enabled

DBCC TRACESTATUS

Become

How to ready ON or OFF

In one case you have pointed required trace flag and done enough R&D well-nigh its beliefs then you can gear up it ON. In that location are three options to ENABLE or DISABLE a trace flag.

DBCC TRACEON
At session level
Every bit a Startup parameter

In coming section we will hash out features of these option.

DBCC TRACEON

A DBCC control is available to set a trace ON or OFF. It is swift and requires no restart to take effect. Only drawback is that TRACE status is not persistent among service restart. You continue the trace flag ON only later on a service restart information technology volition be automatically in OFF land.

DBCC TRACEON command has post-obit syntax with parameters

DBCC TRACEON( TraceFlag#, [-ane], [WITH_NO_INFOMSGS])

Parameters definition can be seen here

Nigh of the time you will be required to enable the trace flag globally through -1 parameter. Local trace flags will be discussed in next section. For example i want to enable trace flag # 3226 on my server. It would prevent the log entry of successful backups in SQL Server logs. Following command volition be used for it.

-- Gear up trace # 3226 ON

DBCC TRACEON ( 3226 , - i )

Here i have non used WITH_NO_INFOMSGS parameter. It is optional to use. By using DBCC TRACESTATUS let us verify that required trace is assail at global level.

But keep in mind that trace enabled in this way by using DBCC TRACEON volition get OFF when SQL Server service volition be restarted. And to manually set the trace flag OFF nosotros can again use DBCC TRACEOFF.

-- Prepare trace # 3226 OFF

DBCC TRACEOFF ( 3226 , - one )

Get

At Session Level

Consider the same DBCC TRACEON command that we used in previous section. If we do not use parameter -i in our command then trace flag behavior will be limited but at session level. Most of the time session level trace flag is used to troubleshoot or analyze the effects of trace flag in limited capacity by a dev or DBA. Let us cheque this functionality by using trace flag 3226.

First i would verify that trace flag # 3226 is set OFF.

Now i will enable the trace flag 3226 at session level i.eastward. without specifying the -ane parameter.

We can come across that trace is enabled but not at global level. At this stage if i create a database backup in same session and then fill-in event will not be recorded in SQL Server log and same is purpose of this trace flag 3226.

We can verify in SQL Server logs that no database fill-in issue is recorded there after trace flag 3226 was enabled at session level.

Now let u.s.a. create some other database fill-in out side of same session where we enabled trace flag 3226. Then we will analyze the SQL Server logs.

Refreshing the SQL Server log will show the entry of recent backup there.

At the end we can exit the specific session or set the trace flag 3226 OFF for that session past using DBCC TRACEOFF (3226). An important signal is that non all trace flags tin can be ready at session level. For each specific trace flag you can view this information on BOL almost its availability for session level configuration.

As a Startup Parameter

Another option to prepare a trace flag ON permanently is to set information technology as a startup parameter. Information technology will retain the setting even SQL Server service is restarted. For this purpose nosotros would have to use SQL Server configuration manager.

Open up SQL Server configuration manager
Correct click on SQL Server service of required instance
Go to tab "Startup Parameters"
Add together required trace flag by using -T prefix equally shown below

Now restart the SQL Service and trace flag will be permanently assault even after SQL Server service restarts. Please make sure to utilize upper-case letter letter T in prefix. If you use -t and then trace flag volition silently just non be enabled without any mistake message. To plough OFF the trace flag you volition have to remove it from SQL Server configuration director startup parameters

And of grade a service restart later that.