Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.