SQL Server – Determine Trace Flags Set as Startup Parameter vs. Enabled Globally

dbccsql server

Probably a quick question.

We are looking to test out a couple trace flags as startup parameters in our environment, and while I know we can use DBCC TRACESTATUS to get global-scoped trace flags, it doesn't specify whether this was set at startup or merely set with the -1 flag. Is parsing the SQL error log the only method available? Possibly a registry entry somewhere?

Any way this can be tackled programmatically would be useful.

Thanks for reading!

Best Answer

If you are running SQL Server 2008 R2 SP1 and up then you can query sys.dm_server_registry to find out TRACE FLAGS that are enabled at startup.

select * from sys.dm_server_registry
where 
cast(value_data  as varchar(max)) like '%-T%'

enter image description here

Just incase if you or future visitors need the output in one row then, you can use below T-SQL :

select distinct @@SERVERNAME as SERVER_NAME
                ,replace(STUFF((
                        select ' ' + cast(value_data as varchar(max))
                        from sys.dm_server_registry
                        where cast(value_data as varchar(max)) like '%-T%'
                        for xml PATH('') -- select it as XML
                    ), 1, 1, ' ')        -- This will remove the first character ";" from the result
        , '&#x00', '')                   -- This will remove the "&#x00" else you will get  -traceFlag&#x00
    as TRACE_FLAGS_ENABLED_AT_STARTUP
from sys.dm_server_registry
where cast(value_data as varchar(max)) like '%-T%'

enter image description here

Related Question