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. (https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15) Or delete just records that have problems not whole table)
No responses yet