Running DBCC CHECKDB or DBCC CHECKTABLE on a database may report error 8961. Such a situation could occur while changing the data type of a table column from “ntext to nvarchar(max)” and updating the table with over 4000 records.
Essentially, updating the database table (in SQL Server 2012, 2014, or 2016) with 4000+ records leads to corruption. As a result, you get the following error message:
Msg 8961, Level 16, State 1, LineNumber
Table error: Object ID, indexID, paritionID, allocUnitD (type LOB data). The off-row data node at page (PageID), slot 0, textID does not match its reference from page (PageID), slot 0.
The complete error message looks similar to:
What Causes SQL Error 8961?
This is a corruption bug in the SQL Server engine. The corruption occurs within the Large Object (LOB) column.
How to Fix SQL Error 8961?
Microsoft has released the following cumulative updates to fix the database corruption error 8961:
Cumulative Update 5 for SQL Server 2016 RTM
Cumulative Update 2 for SQL Server 2016 SP1
Cumulative Update 4 for SQL Server 2014 SP2
Cumulative Update 11 for SQL Server 2014 SP1
Cumulative Update 7 for SQL Server 2012 Service Pack 3
Apply the cumulative update based on the SQL Server version you are using.
What If the Error Persists?
If applying the cumulative updates doesn’t help resolve the issue, to work around this issue do the following:
Note: Before trying out the below workarounds, investigate the hardware like drivers for I/O subsystem to check if corruption occurs due to hardware problem. If the hardware is faulty, contact your vendor or hardware manufacturer for further assistance.
- Set the “large value types out of row” Option to 1
After changing the data type, also change the “large value types out of row” option to 1 by executing the query:
ALTER TABLE tbl_Name ALTER COLUMN COLUMN_NAME nvarchar(max) NOT NULL go exec sp_tableoption 'tbl_Name', 'large value types out of row', '1' |
- Restore Records from Backup
If the above workaround fails to fix the error, try resolving the problem by restoring the database from a good known backup. If you don’t have a valid backup, skip to the next method.
- Run DBCC CHECKDB with Repair Option
As a last resort, you may try running the DBCC CHECKDB command using the minimum repair level, i.e., “REPAIR_ALLOW_DATA_LOSS”.
DBCC CHECKDB (‘db_name’, REPAIR_ALLOW_DATA_LOSS) |
But, this may delete the rows from the table.
- Use SQL Database Repair Tool
To repair a severely corrupt SQL database and retrieve all the records, try using a third-party SQL database repair tool. Stellar Repair for MS SQL is one such tool that is built to safely scan and fix a corrupted SQL Server database. It supports repairing a db on SQL Server 2019, 2017, 2016, and earlier versions. The software repairs database files (.mdf and .ndf) and recovers all the objects like table, deleted records, stored procedures, etc.
Conclusion
DBCC CHECKDB may report database corruption error 8961 when the data type of a table column is changed and the table is updated with 4000+ rows. The issue occurs due to a corruption bug within the SQL Server engine. To fix this, try installing the latest cumulative updates released by Microsoft. If this doesn’t work, try the workarounds discussed above to fix the 8961 error. If you are required to repair the database, a better alternative is to use SQL database repair tool. The tool can help fix the error with no added risk of data loss.
