INF: Description of the DBCC PGLINKAGE Command

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server versions 4.2, 4.21, and 4.21a

SUMMARY

The information below is an elaboration on Sybase's description of the database consistency checker (DBCC) commands (taken from the Sybase internals course "SQL Server Diagnostic Manual").

This article discusses some basic tips and instructions on how to use the DBCC PGLINKAGE command.

MORE INFORMATION

NAME: DBCC PGLINKAGE

VERSIONS DBCC PGLINKAGE AVAILABLE IN:

   DBCC PGLINKAGE is available in versions 1.1x and 4.2 of SQL Server.

   DBCC PGLINKAGE follows a SQL server's page linkage. Starting with a
   pre-specified page, it will either follow the next page pointer or
   the previous page pointer. Options exist that will print all page
   numbers that are being traversed by PGLINKAGE. If there is a break
   before the end of a chain, PGLINKAGE will show were the break is.

   dbcc pglinkage( dbid, start, number, print option, target, order)

   dbid - Enter the database ID of the database in which the page
          chain is to be followed.

   start - This is the page number in decimal, where to start
           following the chain.

   number - Enter the number of pages to be followed. Enter a 0 if the
            chain is to be followed to the end. Also, enter a 0 if the
            target (see below) parameter is specified.

   print option - Print option can either be 0, 1, or 2.

                  0 - With this option, all that is displayed is the
                      number of pages that were scanned.
                  1 - This option will display the last 16 page
                      numbers of pages that were found to be in the
                      chain.
                  2 - This option will print out the page number of
                      every page traversed in the chain.

   target - If a number other than 0 is entered for this option,
            PGLINKAGE will follow the page chain until this page
            number is reached or the end of the chain is reached.

   order - Enter a 0 for this option if previous page pointers are to
           be followed. Enter a 1 if next page pointers are to be
           followed.

Version 4.2

As with most undocumented DBCC commands, the DBCC command TRACEON must be used in order to get information: DBCC TRACEON(3604) must be used for information to be returned to a front-end's result area, and DBCC TRACEON(3605) must be used for the information to be written to the errorlog.

The most prevalent use of DBCC PGLINKAGE is when trying to find a break in an object's page chain. To find useful pages in which to start a page chain search, examine sysindexes and get the columns "first," "root," and "indid," where the ID is the object in question. The data chain's indid will be 0 or 1. If the indid is larger than 1, the chain is a nonclustered index. If the indid equals 0, the column "first" will be the first page in the data chain and the column "root" will be the last page in the data chain.

However, if the indid equals 1, the column root is the root page of the clustered index for that object. With a clustered index, it is impossible to obtain the last page in a data chain if there is a break in the next page pointers. You must drop the clustered index in order for the column root to hold the last page of a chain.

A word of caution: If the clustered index is dropped, it cannot be used in the possible retrieval of lost data. On the other hand, if it is dropped, the last page in the data chain will be obtained. With this information, the tail of the chain can be traversed to the break, using previous page pointers. The tail can then be recovered by changing the column "first" to be the page in the chain where the break is.

Also, never run more then one instance of DBCC PGLINKAGE at a time or you will get spurious errors.

Hint when using the parameter order: When following a chain from the first page, the order should be 1. When following a chain from the last page in a chain, the order should be 0.

Versions 1.1x

The uses and parameters are the same as in version 4.2.

Example

Below is an example of the use of DBCC PGLINKAGE. The information returned is the page linkage of a master database's sysobjects. Other master databases could very easily have a different linking pattern. The object ID of sysobjects is 1, and the first page is also 1. The order used is from the first page following the next page pointers.

dbcc traceon(3604) go dbcc pglinkage(1,1,0,2,0,1) go

Object ID for pages in this chain = 1. Page : 1 Page : 6 Page : 2 Page : 7 Page : 3 Page : 4 Page : 5 End of chain reached. 7 pages scanned. Object ID = 1. Last page in scan = 5. DBCC execution completed. If DBCC printed error messages, see your System Administrator. (Msg 2528, Level 0, State 1).