Sybase: show active trace flags

Sybase: show active trace flags

Posted on  October 23, 2012  by hb

If you need to know which trace flags are active, you can use the dbcc traceflags command. Before using it, you show switch on the trace flag 3604 to display the output to standard output (i.e. the console).

1 2 3 4 5 6 7 8 9 10

1 > use master 2 > go 1 > dbcc traceon ( 3604 ) 2 > go DBCC execution completed . If DBCC printed error messages , contact a user with System Administrator ( SA ) role . 1 > dbcc traceflags 2 > go Active traceflags : 3604 , 7717   DBCC execution completed . If DBCC printed error messages , contact a user with System Administrator ( SA ) role .

If you do not switch on the trace flag 3604, you'll see the following:

1 2 3

1 > dbcc traceflags 2 > go DBCC execution completed . If DBCC printed error messages , contact a user with System Administrator ( SA ) role .

Instead of trace flag 3604 you can also use the flag 3605. The ouput will then be writte to the error log:

1 2 3 4 5 6 7 8

1 > use master 2 > go 1 > dbcc traceon ( 3605 ) 2 > go DBCC execution completed . If DBCC printed error messages , contact a user with System Administrator ( SA ) role . 1 > dbcc traceflags 2 > go DBCC execution completed . If DBCC printed error messages , contact a user with System Administrator ( SA ) role .

And in the error log:

1 2	00 : 00 : 00000 : 00027 : 2012 / 10 / 22 17 : 19 : 41.28 server   DBCC TRACEON 3605 , SPID 27 Active traceflags : 3604 , 3605 , 7717

You'll notice that it also returns 3604 although it is not active in our session (the ouput wasn't written to the console). Actually trace 3604 is a global trace. Switching it off in another session will also disable it in this session. But switching it on in another session, will not have the output displayed on the console for this session. No clue why...

Please also note that you can switch on the trace flags only for a session using the following:

1 2 3 4

1 > set switch on 302 2 > go Switch 302 ( 'print_plan_index_selection' ) is turned on . All supplied switches are successfully turned on .

This will switch on the trace flag for this session but it will not be visible with dbcc traceflags (even in this session):

1 2 3 4 5

1 > dbcc traceflags 2 > go Active traceflags : 3604 , 7717   DBCC execution completed . If DBCC printed error messages , contact a user with System Administrator ( SA ) role .