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
>
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
>
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
>
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:
|
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
>
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
>
dbcc
traceflags
2
>
go
Active
traceflags
:
3604
,
7717
DBCC
execution
completed
.
If
DBCC
printed
error
messages
,
contact
a
user
with
System
Administrator
(
SA
)
role
.
|