Introduction
This blogs guides to repair table when index and page level error occures in a table in SQL Server and provides the SQL query for fixing table issue
Getting Started
One day I repaired my database as it was gone to suspect mode, in my previous blog (Database in Suspect Mode) I have written how to repair or recover the database when it goes to suspect mode. After that when I execute the select query, from some tables I could succeed fetching data, but some tables give below error.
Attempt to fetch logical page (1:27232) in database 23 failed. It belongs to allocation unit 72057594049069056 not to 281474980642816.
Then I used the DBCC CHECKDB to check database error, I got some results like below. I got this below error for the 7 tables.
Msg 605, Level 12, State 3, Line 1
Attempt to fetch logical page (1:27232) in database 22 failed. It belongs to allocation unit 72057594049069056 not to 281474980642816.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Region
This can happen when CHECKDB is trying to read a page containing a row-over column to construct a complete copy of a row in-memory. The error can happen for a number of reasons, all related to IO subsystem problems - for example, a lost write.
These errors are typically caused by some sort of database corruption, either in-memory or on-disk. Essentially, the server is requesting a page from the buffer pool, and the page number/object ID on the page differs from the one that was requested.
Solution
The solution is to repair tables listed in the error list. Repair all the table one by one. Execute DBCC CHECKTABLE on the table specified in the error message to determine the full extent of the corruption, then perform a repair on the specified table. If the error involves a data page, it may be necessary to use the REPAIR_ALLOW_DATA_LOSS clause or If the error involves an index page, use the REPAIR_REBUILD clause with the DBCC CHECKTABLE SQL Command.
SQL Server Repair Tables
Table repair in SQL Server is not a difficult task, it very easy. You need to follow only 3 steps to repair tables. Check the below steps to which is used to repair tables.
Fixing table issue by repair table.
The below SQL query helps for fixing table’s above mentioned error in SQL server. Note that before repair table by running these SQL query, take backup of your SQL database.
Use Database_Name
ALTER DATABASE Database_Name SET SINGLE_USER with ROLLBACK IMMEDIATE
DBCC CheckTable (‘Table_Name', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE Database_Name SET MULTI_USER
After executing above SQL query again check the database health with the execute DBCC CHECKDB as soon as possible. Some results you will get like below, find the error message that you were getting before running the SQL commands, If same error is not reflecting, then your table is successfully repaired, and your problem is gone.
DBCC results for 'Digiphoto'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
Msg 8905, Level 16, State 1, Line 1
Extent (1:22488) in database ID 5 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Msg 8906, Level 16, State 1, Line 1
Page (1:16702) in database ID 5 is allocated in the SGAM (1:3) and PFS (1:16176), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
CHECKDB found 2 allocation errors and 0 consistency errors not associated with any single object.
DBCC results for 'sys.sysrscols'.
There are 2864 rows in 72 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 435 rows in 6 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 555 rows in 14 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 3 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysfgfrag'.
There are 4 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysphfg'.
There are 2 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 3 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 130 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 1693 rows in 33 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 5739 rows in 146 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 47 rows in 24 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 65 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 970 rows in 19 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 1291 rows in 8 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 2157 rows in 895 pages for object "sys.sysobjvalues".
Thanks