Returning DBCC Messages to SSMS
Published: Nov 20, 2016
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron