A table in MS SQL database is a group of specified number of rows and columns which contain major chunk of records. A DBA can fetch the required records as per his/her requirements from any table, by running a query. A database query while in most cases displays desirable results, might fail due to various reasons. One of the most common factors for failure is corruption of table. Few known factors for table corruption are improper system shutdown, metadata structure corruption, application failure, and virus attack. In most cases of query execution failure, an error message gets displayed on the monitor. This error message comprises of all necessary information that helps a DBA to identify the level of corruption and the way to overcome it. However, if in case a DBA is unable to understand the error message and has no backup of database, then SQL database recovery becomes a little difficult.
To explain the above error message, here is a real-time scenario. A user wants to count the number of rows of table for which he executes count(*) query. But, the query instead of displaying the total number displays the below error message:
"Attempt to fetch logical page (1:42724) in database 'test' belongs to object 'report-table', not to object 'RRR'. Connection Broken.."
Understanding that the table is corrupted, the user tries DBCC Checktable command with repair_rebuild parameter to know the level of corruption. The command displays the below result:
"Server: Msg 2535, Level 16, State 1, Line 1Â
Table Corrupt: Page (1:42724) is allocated to object ID 1517248460, index ID 0, not to object ID 982294559, index ID 0 found in page header.Â
Server: Msg 8928, Level 16, State 1, Line 1Â
Object ID 1517248460, index ID 0: Page (1:42723) could not be processed. See other errors for details.Â
Server: Msg 8939, Level 16, State 1, Line 1Â
Table Corrupt: Object ID 1517248460, index ID 0, page (1:42723). Test (m_freeCnt == freeCnt) failed. Values are 1468 and 8066.Â
Server: Msg 8939, Level 16, State 1, Line 1Â
Table Corrupt: Object ID 1517248460, index ID 0, page (1:42723). Test (emptySlotCnt == 0) failed. Values are 15 and 0........."
The database table records are inaccessible post the above error message appears. The user also tries DBCC CHECKDB command with repair_allow_data_loss parameter. But nothing works.
Resolution:
In such situations, the best and safest way is to repair the database table is by using SQL Recovery software.
To explain the above error message, here is a real-time scenario. A user wants to count the number of rows of table for which he executes count(*) query. But, the query instead of displaying the total number displays the below error message:
"Attempt to fetch logical page (1:42724) in database 'test' belongs to object 'report-table', not to object 'RRR'. Connection Broken.."
Understanding that the table is corrupted, the user tries DBCC Checktable command with repair_rebuild parameter to know the level of corruption. The command displays the below result:
"Server: Msg 2535, Level 16, State 1, Line 1Â
Table Corrupt: Page (1:42724) is allocated to object ID 1517248460, index ID 0, not to object ID 982294559, index ID 0 found in page header.Â
Server: Msg 8928, Level 16, State 1, Line 1Â
Object ID 1517248460, index ID 0: Page (1:42723) could not be processed. See other errors for details.Â
Server: Msg 8939, Level 16, State 1, Line 1Â
Table Corrupt: Object ID 1517248460, index ID 0, page (1:42723). Test (m_freeCnt == freeCnt) failed. Values are 1468 and 8066.Â
Server: Msg 8939, Level 16, State 1, Line 1Â
Table Corrupt: Object ID 1517248460, index ID 0, page (1:42723). Test (emptySlotCnt == 0) failed. Values are 15 and 0........."
The database table records are inaccessible post the above error message appears. The user also tries DBCC CHECKDB command with repair_allow_data_loss parameter. But nothing works.
Resolution:
In such situations, the best and safest way is to repair the database table is by using SQL Recovery software.
Source...