The sysoptions.number column 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
