Why we should enable trace flag 3604 before part of DBCC statements?

In many cases, while we using DBCC commands, we must to run two statements instead of one, in order to receive the wishful result.

For example, in order to receive database information, we write:

DBCC TRACEON (3604);

GO

DBCC DBINFO (‘AdventureWorks2012’);

GO

instead of simple:

DBCC DBINFO (‘AdventureWorks2012’);

GO

So, why we need to use additional statement, why we enable trace flag 3604 before part of the DBCC commands?

The answer consists of two parts:

1. Most of the trace flags can be turned on/off for all connections (global), by giving parameter -1:

DBCC TRACEON (3604, -1);

GO

 and for specific session only, without parameter -1:

DBCC TRACEON (3604);

GO

2. Part of DBCC statements (or commands) designed to send their output by default to the SQL Server ERRORLOG, trace listener or debugger. Enabling of trace flag 3604, without parameter -1, tell to SQL server engine – print the output in query window for this specific session.

* Enabled trace flag would be cleared, after session ending.

In simple words, by running of two DBCC statements, we tell to SQL server “print something for me” and “do it in this specific window”.

 —————————————————————————-

Trace Flags list – https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

#sql #server #Learning #trace #DBCC #3604 #statement #command #sqlserver #traceflag #flag #DBA #databaseadministrationservice