FIX: Changing the data type and then updating the table with more than 4,000 records causes database corruption

Symptoms

You change the data type of a column in a database table from ntext to nvarchar(max) on a server that is running Microsoft SQL Server 2012. When you update the table by adding more than 4,000 records, DBCC CHECKDB may report errors that resemble the following:

Msg 8961, Level 16, State 1, Line 22
Table error: Object ID 617769258, index ID 1, partition ID 72057596040970240, alloc unit ID 72057596054339584 (type LOB data). The off-row data node at page (1:139), slot 0, text ID 729458016780288 does not match its reference from page (1:192), slot 0.

Msg 8961, Level 16, State 1, Line 22
Table error: Object ID 617769258, index ID 1, partition ID 72057596040970240, alloc unit ID 72057596054339584 (type LOB data). The off-row data node at page (1:179), slot 0, text ID 729458016780288 does not match its reference from page (1:192), slot 0.

Msg 8929, Level 16, State 1, Line 22
Object ID 617769258, index ID 1, partition ID 72057596040970240, alloc unit ID 72057596054274048 (type In-row data): Errors found in off-row data with ID 166508063358976 owned by data record identified by RID = (1:192:0)
DBCC results for 'TEST'.

There are 1 rows in 1 pages for object "TEST".
CHECKTABLE found 0 allocation errors and 3 consistency errors in table 'TEST' (object ID 617769258).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (TestDatabase.dbo.TEST).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Resolution

This update is included in Cumulative Update 7 for SQL Server 2012 Service Pack 3.


About SQL Server 2012 builds

Workaround

To work around this issue after you change the data type, set the large value types out of row option to 1.

ALTER TABLE TEST ALTER COLUMN COLUMN_NAME nvarchar(max) NOT NULL 
go
exec sp_tableoption 'TEST', 'large value types out of row', '1'

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References

Learn about the terminology Microsoft uses to describe software updates.
Tulajdonságok

Cikkazonosító: 3213240 - Utolsó ellenőrzés: 2017. jan. 17. - Verziószám: 7

Visszajelzés