Returning DBCC Messages to SSMS

There are some DBCC commands, such as DBCC PAGE which seem not to do anything because you see nothing in SSMS to tell you otherwise. As it turns out this isn’t the case just that SQL Server is suppressing the information messages.

This is where we introduce TF 3604 which solves this exact problem.

Let’s show this with an example using DBCC PAGE as mentioned above.

Firstly we need a page in order to be able to actually use DBCC PAGE, so we’ll get that using DBCC IND and look at the sales.salesOrderHeader table in my AdventureWorks2012 database…

use AdventureWorks2012
go

-- Run the DBCC command
dbcc ind('AdventureWorks2012', 'sales.salesOrderHeader', 1)
go

From this I’m going to just pick a random page and use that. I’ll use 12691.

So let’s crack open the page and have a look…

use AdventureWorks2012
go

dbcc page('AdventureWorks2012', 1, 12691, 3)
go

Okay, I wanted to see what was in the page but I’ve got nothing. What gives? Well, we’ve not told SQL Server to return the output to the messages window and hence this is exactly why we need TF 3604 to save the day…

dbcc traceon(3604)

dbcc page('AdventureWorks2012', 1, 12691, 3)
go


Bingo!!!

As you can see, TF 3604 can be very useful if you’re running DBCC commands, expect something to be returned, but are not seeing any output.