Running DBCC CHECKDB and get out of TEMPDB errors although having enough disk space.

CHKDSK commands also cannot find any bad sector but I cannot perform SELECT some of my tables and I cannot perform BACKUP.

So you can use 2 undocumented SQL Command to detect which page has error:

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

  • 0 – print just the page header
  • 1 – page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn’t have one, like allocation bitmaps)
  • 2 – page header plus whole page hex dump
  • 3 – page header plus detailed per-row interpretation

DBCC IND (‘DBName’ or DBID, ‘TableName’ or ObjectId, NOn Clustred Index ID)

The third parameter can be the Non Clustered Index Id from Sys.indexes table or 1 or 0 or -1 or -2.  -1 provides complete information about all type of pages ( in row data, row over flow data, IAM, all indexes ) associated with the table.

Explanation of the what does each column mean: 

PageFID — the file ID of the page

PagePID — the page number in the file

IAMFID — the file ID of the IAM page that maps this page (this will be NULL for IAM pages themselves as they’re not self-referential)

IAMPID — the page number in the file of the IAM page that maps this page

ObjectID — the ID of the object this page is part of

IndexID — the ID of the index this page is part of

PartitionNumber — the partition number (as defined by the partitioning scheme for the index) of the partition this page is part of

PartitionID — the internal ID of the partition this page is part of

iam_chain_type — see IAM chains and allocation units in SQL Server 2005

PageType — the page type. Some common ones are:

                  1 – data page  2 – index page 3 and 4 – text pages 8 – GAM page 9 – SGAM page 
                  10 – IAM    page 11 – PFS page

IndexLevel — what level the page is at in the index (if at all). Remember that index levels go from 0 at the   leaf to N at the root page (except in clustered indexes in SQL Server 2000 and 7.0 – where there’s a 0 at the leaf level (data pages) and a 0 at the next level up (first level of index pages))

NextPageFID and NextPagePID — the page ID of the next page in the doubly-linked list of pages at this level of the index

PrevPageFID and PrevPagePID — the page ID of the previous page in the doubly-linked list of pages at this   level of the index

As summary these are the steps you may follow:

Cannot run DBCC and backup

Run dbcc page command to find which page you have problem

    If it is an index page simple delete index and recreate.

    If it is data page keep on following:

You can run DBCC CHECKDB with allow_data_loss parameter. (  Or delete just records that have problems not whole table)

No responses yet

Bir cevap yazın

E-posta hesabınız yayımlanmayacak.