SQL Trace Flags and ConfigMgr
Hello everyone, ConfigNinja here. In one of my old blog posts I wrote a little bit about the usage of SQL Trace Flags in ConfigMgr.
Q. First what is a SQL Trace Flag?
A. Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. Use them only when advised by a Microsoft support professional. Trace flags are used for a wide variety of purposes in Microsoft® SQL Server. This use ranges from activating certain behavioral fixes to capturing data for analysis during support issues.
Recommended Reading
Books Online: Trace Flags (Transact-SQL)
Books Online: Using the SQL Server Service Startup Options
During the installation of ConfigMgr in the ConfigMgrSetup.log you will see the following Message:
Meaning of T8295:
Creates a secondary index of the identifying columns on the change tracking side table at enable time. This trace flags allow the creation of a secondary index for change tracking tables, which is used for replication between ConfigMgr Sites.
Meaning of T4199:
Controls multiple query optimizer changes previously made under multiple trace flags. For more information, see this Microsoft Support article Scope: global or session.
These are the only two trace flags that should be on the SQL Server instance that supports ConfigMgr site database, If you found other trace flags you can disable them.
How to check current trace flags:
Open SQL Management Studio > New Query
DBCC TRACESTATUS (-1)
go
As you can see here it only show the two trace flags that ConfigMgr requires, If it display other trace flags you may disable them by running the following command.
How to disable a trace flag.
DBCC TRACEOFF (XXXX)
go
or
DBCC TRACEOFF (XXXX,-1)
go
Perform a Trace Status query again to validate it has been disabled and only ConfigMgr Trace Flags are enable.
if you need to enable a trace flag perform the following steps.
How to enable a trace flag
DBCC TRACECON (XXXX)
Hope this help out, was a quick blog post, so I hope you find it useful.
Santos Martinez – Sr. PFE – ConfigMgr and Databases
Steven Hernandez – PFE – ConfigMgr Contributor
Disclaimer: The information on this site is provided "AS IS" with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of any included script samples are subject to the terms specified in the Terms of Use