3 ways to have fun with DBCC PAGE

07 January,2016 by Jack Vamvas

A very useful tool is DBCC PAGE. It is used to view database page contents

The basic syntax is :

dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Officially it is an undocumented command. But it’s been around for awhile and there is great information across forums and other blogs discussing techniques using DBCC PAGE. It is heavily used in database corruption troubleshooting and storage layer analysis

Here are 3 ways I’ve used DBCC PAGE recently. But there is a wealth of information available , which can be explored

To view the output from DBCC PAGE you'll need to run : DBCC TRACEON(3604)

Find last known good DBCC CHECKDB

dbcc page ('master',1,9,3)

Read more Why is SQL Server executing DBCC CHECKDB against my database at startup?

 

View Page Free Space (PFS) pages.

SQL Server uses PFS pages to track the amount of space still available on each page

DBCC PAGE (master, 1, 1, 3);

 

View what’s on a page

--get PagePID

DBCC IND('Master','tRoleMember',1)

dbcc page ('master',1,460,2)

Hours of fun and a great way to start exploring and learning about SQL internals

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on 3 ways to have fun with DBCC PAGE


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer