Inspecting database file contents

Pages in a database are returned by the sys.dm_db_database_page_allocations DMF. Parameters:

  • @databaseId – SMALLINT, mandatory
  • @tableId – INT, optional (table's object_id; default is all tables in database)
  • @indexId – INT, optional (default is all indexes)
  • @partitionId – INT, optional (default is all partitions)
  • @modemandatory; either 'DETAILED' OR 'LIMITED'
SELECT * 
FROM sys.dm_db_database_page_allocations(
  DB_ID(), OBJECT_ID('[dbo].[Test]'), NULL, NULL, 'DETAILED'
)
WHERE is_allocated = 1;

DBCC IND is available in earlier versions and returns a subset of the information in sys.dm_db_database_page_allocations. Parameters:

  • database ID – SMALLINT, mandatory, literals only
  • object ID/name – INT/NVARCHAR, mandatory, literals only
  • index ID – INT, mandatory, literals only
DBCC IND(7, 'dbo.Test', 1) WITH TABLERESULTS

Correspondence to columns in sys.dm_db_database_page_allocations:

DBCC IND column name Returned in sys.dm_db_database_page_allocations as
PageFID allocated_page_file_id
PagePID allocated_page_page_id
IAMFID allocated_page_iam_file_id
IAMPID allocated_page_iam_page_id
ObjectID object_id
IndexID index_id
PartitionNumber partition_id
PartitionID rowset_id
iam_chain_type allocation_unit_type_desc
PageType page_type
IndexLevel page_level
NextPageFID next_page_file_id
NextPagePID next_page_page_id
PrevPageFID previous_page_file_id
PrevPagePID previous_page_page_id

To cache DBCC IND output for reuse:

IF OBJECT_ID('tempdb..#dbccInd') IS NOT NULL
  DROP TABLE #dbccInd
 
CREATE TABLE #dbccInd (
  allocated_page_file_id SMALLINT         -- PageFID
, allocated_page_page_id INT              -- PagePID
, allocated_page_iam_file_id SMALLINT     -- IAMFID
, allocated_page_iam_page_id INT          -- IAMPID
, [object_id] INT                         -- ObjectID
, index_id INT                            -- IndexID
, [partition_id] INT                      -- PartitionNumber
, rowset_id BIGINT                        -- PartitionID
, allocation_unit_type_desc NVARCHAR(60)  -- iam_chain_type
, page_type INT                           -- PageType
, page_level TINYINT                      -- IndexLevel
, next_page_file_id SMALLINT              -- NextPageFID
, next_page_page_id INT                   -- NextPagePID
, previous_page_file_id SMALLINT          -- PrevPageFID
, previous_page_page_id INT               -- PrevPagePID
)
 
INSERT INTO #dbccInd
EXEC ('DBCC IND(7, ''dbo.Test'', 1) WITH TABLERESULTS')
 
--SELECT * FROM #dbccInd

Page contents are inspected using DBCC PAGE. Parameters:

  • database ID/name – from sys.databases or DB_ID()
  • file ID – for selected page as given in allocated_page_file_id (PageFID) above
  • page number – for selected page as given in allocated_page_page_id (PagePID) above
  • print option; one of:
    • 0 – page and buffer header fields
    • 2 – page and buffer header fields, whole page as bytes, slot directory (row offset table)
    • 1 – page and buffer header fields, each row as bytes, slot directory (row offset table)
    • 3 – page and buffer header fields, each row as bytes then as column values, slot directory (row offset table)

To print output to the console, enable trace flag 3604:

DBCC TRACEON (3604)
DBCC PAGE (7, 1, 289,2) 
DBCC TRACEOFF (3604)

To cache output in a table:

IF OBJECT_ID('tempdb..#dbccInd') IS NOT NULL
  DROP TABLE #dbccInd
 
CREATE TABLE #dbccInd (
  [ParentObject] VARCHAR(100)
, [Object] VARCHAR(1000)
, [Field] VARCHAR(100)
, [VALUE] VARCHAR(MAX)
);
 
INSERT INTO #dbccInd
EXEC sp_executesql N'DBCC PAGE (7, 1, 289,2) WITH TABLERESULTS'
 
--SELECT * FROM #dbccInd