Displaying Currently Set Switches with sysoptions

The number column of sysoptions contains the switch ID for currently set switches.

sysoptions shows these switches:

Trace flag set in the runserver file with the -T flag
Trace flag set with dbcc traceon(flag_number) or set switch serverwide on
Trace flags and switches set for a specific system process ID (spid) with set switch on

sysoptions shows only the switches that are visible to the user. Users cannot see switches set privately by other spids. The value for number is Null for all option categories other than switches.

sysoptions displays this information for switches:

spid – spid for the current session.
name – name of the switch. If an unnamed switch is set, name contains the number of the switch converted to a string.
category – specifies the string Switch.
currentsetting – set to 1 if the switch is set, 0 if the switch is not set.
defaultsetting – contains 0.
scope – indicates whether the switch is set server-wide or for the session. Values are:
- 0 – switch is not set.
- 8 – switch is set server-wide.
- 16 – switch is set privately to the current spid.
- 24 – switch is set server-wide and privately.
number – contains the switch ID as an integer.

This query displays all sysoptions entries for switches:

select * from sysoptions
where category = 'Switch'

This query displays the switches set for the current session:

select * from sysoptions
where category = 'Switch'
and currentsetting = '1'

This shows the sysoptions output after setting trace flag 3604:

spid       name                              category          currentsetting      defaultsetting         scope               number
------     ----------------------------     ---------------  ---------------
      ----------------       ------------         --------------
    11           print_output_to_client              Switch                1
                     0                  8                                3604