4

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!

7

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

| improve this answer | |
  • Never heard of sys.dm_server_registry. Nice! We've got some 2008's out there but it looks like some spelunking the registry will be the way to go. – FilamentUnities May 20 '15 at 21:22
  • For older versions, you have to query the registry hive. SQLArgn - where n increases in number with each additional startup parameter. – Kin Shah May 20 '15 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.