Error 7902

Severity

16

Message text

Versions earlier than 15.0:

Data size mismatch occurred while checking TEXT/IMAGE values. The first page for this value is: %ld. There were %ld bytes found, which is different from the expected data length of %ld bytes.

Version 15.0 and later:

Data size mismatch while checking text/image/unitext values. The first page for this value is %d, partition ID %d. %d bytes found; however, the expected data length is %d bytes.

Explanation

This error occurs when Adaptive Server detects a mismatch between the expected length of the text or image data of a text column and its actual length. The length of the text or image data is stored on the page header of the text or image data page.

This error usually occurs in one of the following situations:

• When you run dbcc checktable on a table whose text or image column was updated to NULL and then updated again to a non-NULL value via a writetext command which did not commit.

• When you run dbcc checktable or dbcc checkdb and your text or image data is corrupted. If this is the case, check your hardware error log and your operating system error log and determine the cause of the 7902 error.

Prevention

Consider using writetext with log or update instead of writetext with no log. This way, you will not have unlogged changes to your database and will not get 7902 errors if writetext with no log did not commit.

Action

To eliminate the 7902 error, perform the following steps for each table encountering it:

1. Record the page numbers reported by error 7902 for your table.

2. Select the offending rows from the table in a separate temporary table, giving the name of the text column for the textptr function:

   1> select * into #<newtable> from <mytable> 
   2> where convert (int, textptr(<text_colname>)) 
   3> in (<list_of_page#'s>)
   4> go
   

   The list of page numbers in the above query is the enumeration of all the page numbers specified by the 7902 errors. <text_colname> is the name of the text column.

3. Update the corrupted text or image data from the old table with the text or image data from the new table:

   1> begin transaction
   2> go
   

   1> update <mytable> 
   2> set <mytable>.<text_col> = #<newtable>.<text_col> 
   3> from #<newtable> 
   4> where <mytable>.<other_col> =
   5>#<newtable>.<other_col> 
   6> go
   

   Verify that only the required number of rows are updated by the above query, then commit the transaction. <other_col> in the above query is the name of another column in the table that uniquely identifies a row (like a primary key). If you do not have any primary keys on the old table, run the above query with several search conditions in the where clause:

   1> update <mytable> 
   2> set <mytable>.<text_col> = #<newtable>.<text_col> 
   3> from #<newtable> 
   4> where <mytable>.<col1> = #<newtable>.<col1> 
   5> and <mytable>.<col2> = #<newtable>.<col2> 
   6> go 
   

4. Make sure that only the required number of rows are updated by the above query and then commit the transaction:

   1> commit transaction
   2> go
   

You will see the following messages during dbcc checktable:

Checking mytable

Msg 7902, Level 16, State 1:

Server 'PRODUCTION', Line 1:

Data size mismatch occurred while checking 
TEXT/IMAGE values. The first page for this value 
is: 321. There were 12 bytes found, which is 
different from the expected data length of 0 bytes.

Msg 7902, Level 16, State 1:

Server 'PRODUCTION', Line 1:

Data size mismatch occurred while checking
 TEXT/IMAGE values. The first page for this value 
is: 291. There were 17491 bytes found, which is 
different from the expected data length
 of 19291 bytes. 

Run the following query to select the erroneous rows from <mytable> into #<newtable>:

1> select * into #<newtable> from <mytable> 
2> where convert (int, textptr(<text_colname>)) 
3> in (321, 291)
4> go

An alternate method is to select all data from the old table into a new table.

If the index ID is 0 or 255 and you do not have clean backups of your database, copy the data from the corrupted table into a new (dummy) table or into a file. Then rename your old, corrupted table and copy the data back into a new table using the original name. For more information about doing this, refer to “Rescuing data from a corrupted table” in the chapter “Other Useful Tasks” in the most recent version of Troubleshooting and Disaster Recovery for details.

Before dumping your database, make sure it is working correctly. More specifically, the following commands should be run prior to each dump:

• dbcc checkdb.

• dbcc checkalloc or dbcc checkalloc with the fix option. Refer to “dbcc” in the Reference Manual: Commands for information about dbcc checkalloc.

Additional information

For more information, refer to “select into”, “convert”, and “writetext” in the Reference Manual: Commands and in the Transact-SQL User's Guide.

Versions in which this error is raised

All versions