SQL Server Find page chain

1. View the file number of the current record, page page, and slot in the page based on the data record.

Examples are as follows:

Ten %%physloc%%, Sys.fn_physlocformatter (%%physloc%%) as RID from TableName

--Note: In the 64-bit system, the format of Sys.fn_physlocformatter is sometimes wrong, it needs to be calculated by hand according to Physloc, the method of calculation is

Flashbacks in bytes, such as

0x0702000001002200 flashbacks after the
0x0022000100000207

The first four bits 0022 indicates the slot number 2*16+2 = 34, the next four bits 0001 represents the file number, and the remaining 00000207 indicates the file number 2*16*16+7 = 519

DBCC TRACEON (3604) DBCC page (database name,1,40995,0) DBCC TRACEOFF ( 3604)

2. Get home Based on sys.system_internals_allocation_units (this view with sys.allocation_units)

Select  as Name,p.rows,a.type_desc, A.total_pages,a.first_page,a.first_iam_page,a.root_page  from  = a.container_idwhere object_id = object_id ('dbo. Dumplpt')

The first page number is saved in 16, the first two groups represent 2-byte file numbers, and the last 4 groups represent the page numbers, as in the reverse order of bytes. You can use the following functions to return file numbers and page numbers:

CREATE FUNCTION convert_page_nums (@page_num binary (6)) RETURNS varchar ( One) as BEGIN RETURN (CONVERT (varchar (2), (CONVERT (int, substring (@page_num,6,1)) * Power (2,8)) +(CONVERT (int, substring (@page_num,5,1)))) +':'+CONVERT (varchar ( One), (CONVERT (int, substring (@page_num,4,1)) * Power (2, -)) +(CONVERT (int, substring (@page_num,3,1)) * Power (2, -)) +(CONVERT (int, substring (@page_num,2,1)) * Power (2,8)) +(CONVERT (int, substring (@page_num,1,1))) ( END);

3. Use the DBCC IND command with the following example:

  DBCC IND (testlogdb,'dbo. Dumplpt',-1)

The return shape will be the following result:

Pagefidpagepidiamfidiampidobjectidindexidpartitionnumberpartitionidiam_chain_  Typepagetypeindexlevelnextpagefidnextpagepidprevpagefidprevpagepid1197nullnull695752860172057594038976512in-row Data10null000011961197695752860172057594038976512in-row Data10000011981197695752860172057594038976512in-row Data10000011991197695752860172057594038976512in-row Data10000012001197695752860172057594038976512in-row Data10000012011197695752860172057594038976512in-row Data10000012021197695752860172057594038976512in-row Data10000012031197695752860172057594038976512in-row Data10000012041197695752860172057594038976512in-row Data10000012081197695752860172057594038976512in-row Data10000012091197695752860172057594038976512in-row Data10000012101197695752860172057594038976512in-row Data10000012111197695752860172057594038976512in-row Data10000012121197695752860172057594038976512in-row Data10000012131197695752860172057594038976512in-row data100000

　　

SQL Server Find page chain