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' |
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) |
- 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.
