Why do we need trace flag 3604 for DBCC statements?

For many DBCC statements, we need to enable trace flag 3604 before executing the DBCC command. This is done via:

DBCC TRACEON(3604)
GO

But why do we need this? Some DBCC commands are designed to send the output to the log, attached debugger, or a trace listener. This statement simply instructs them to redirect the output to the client executing the command – most often SQL Server Management Studio (SSMS). Not all default to the SQL Server ERRORLOG. For instance, when running DBCC PAGE, the output does not go to the error log nor the screen unless directed to do so. So how do we direct the output to the error log? To direct the output to the error log, use trace flag 3605:

DBCC TRACEON(3605)
GO

To turn off both for your session, use DBCC TRACEOFF(####).

Trace flags can be enabled/disabled for just the session or globally for everyone connected to the SQL Server. Though 3604 and 3605 are typically enabled only for the session of the person using them, they are no exception and can be enabled globally. To enable a trace flag globally, use –1 as the 2nd parameter like this:

DBCC TRACEON(3604, –1)
GO

This trace flag is now enabled for the session by default and globally. When disabling these, you must use the same format with DBCC TRACEOFF:

DBCC TRACEOFF(3604, –1)
GO

If you omit the –1, you will only turn it off for your session.

If you wish to have trace flags enabled every time SQL Server starts – which is **NOT** recommended – then you must add them to the startup parameters of sqlservr.exe with the –T switch.

-Jay