Day 1 – Trace Flag 3604 & 3605

Today I’m going to cover trace flag 3604 & 3605. These two trace flags are used to print information’s or output from DBCC commands. As far as I know these two trace flags are widely used to get output for DBCC IND & DBCC PAGE commands. These two commands will not print anything unless or until you enable any one of these trace flags

Trace Flag 3604

Enabling this trace flag will give the output of DBCC commands to screen or prints the output to result window. I’m going to enable this trace flag session scoped only. You can check my article “Enable \ Disable Trace Flag in SQL Server” for enabling it in multiple ways.

--Turning on trace flag 3604
DBCC TRACEON(3604)
GO
--Going to check dbid 4(msdb), file id 1 and page 1
DBCC PAGE(4,1,1)

I have now enabled and ran the DBCC command to get 1st page information of msdb database as shown below. If you haven’t turned on this trace flag you won’t be able to see the output.

trace_flag_3604_1

To turn off this trace flag run the command below

--Turning off trace flag
DBCC TRACEOFF(3604)

Trace Flag 3605

This flag is similar to trace flag 3604 which will write the output of the command to errorlog instead of displaying it in SSMS console. I’m going to enable this session scoped only.

--Turning on trace flag 3605
DBCC TRACEON(3605)
GO
--Going to check dbid 4(msdb), file id 1 and page 1
DBCC PAGE(4,1,1)

Now from the output below you can see that the output is not displayed in console.

trace_flag_3605_1

To get the output you need to check errorlog, the output will be written to errorlog as shown below

trace_flag_3605_2

To turn it off use the command below

--Turning off trace flag
DBCC TRACEOFF(3605)

These two trace flags are not documented in MS website, so careful while playing with it. You can use these trace flags to get information from commands to display it in console or to print in errorlog.


Posted

in

by

Comments

3 responses to “Day 1 – Trace Flag 3604 & 3605”

  1. Day 1 – Trace Flag 3604 & 3605 – SQL-Articles…

    Thank you for submitting this cool story – Trackback from DotNetShoutout…

  2. Ashish avatar
    Ashish

    I was just searching some trace related articles and gone through your article. Found one incorrect information,
    to disable the trace, the command should be
    dbcc traceoff (….)
    you might have by mistake mentioned it as traceon for disabling as well.

    1. VidhyaSagar avatar
      VidhyaSagar

      Thanks Ashish, I’ve updated the script.

Leave a Reply to VidhyaSagar Cancel reply

Your email address will not be published. Required fields are marked *