FIX: Column data may be deleted when you update another variable-length column in a table of a database upgraded from SQL Server 2005

Symptoms
When you update a value in a variable-length column in a table of a database that was upgraded from Microsoft SQL Server 2005, data in another variable-length column in the same row is deleted.

This issue occurs in Microsoft SQL Server 2014 and Microsoft SQL Server 2012. The following scenario may expose a database to this issue. This list represents a sequence of events:

  • A database is created in SQL Server 2005.
  • In SQL Server 2005, you alter a table, and then you drop one or more variable-length columns at the end of the table definition.
  • You add new variable-length columns that have a similar data type and length of the dropped columns.
  • The database is upgraded from SQL Server 2005 to SQL Server 2014 or SQL Server 2012.
  • You later update the value of another variable-length column, and the updated data has to overflow to a new page.
In this scenario, the data in one or more of the newly added columns in the same row may be deleted.

You can use the script in the More Information section to verify the following:

  • Whether any tables in a given database have deleted variable-length columns
  • Whether additional variable-length columns have been created that have matching offsets
Note This issue is not discovered by DBCC CHECKDB.
Resolution
Note This fix only prevents future occurrences of the issue.

Important If you are upgrading the database from SQL Server 2005 to SQL Server 2014 or SQL Server 2012, make sure that you refer to the "Workaround" section.

This issue was first fixed in the following cumulative update for SQL Server:

These are the latest cumulative updates for these program versions.

Recommendation: Install the latest cumulative update for SQL Server 2014 or SQL Server 2012

Install the Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included in the previous cumulative update. Check out the latest cumulative updates for SQL Server:



Note For more information about the latest SQL Server builds, see Where to find information about the latest SQL Server builds.
Workaround
Important Apply this workaround when you upgrade the database from SQL Server 2005 to SQL Server 2014 or SQL Server 2012 and before you let UPDATE statements be run in the upgraded database.

To work around this issue if you drop columns in a table, make sure that you do either of the following before you update or delete rows:

  • Rebuild the clustered index by using ALTER INDEX (…) REBUILD
  • Rebuild the heap by using ALTER TABLE (…) REBUILD

More information
You can use the following script to verify whether any tables in a given database have deleted columns that are potentially affected by this issue.

Note If the script does not return any rows, that means that this issue does not currently apply to any table in your system. 
SELECT DISTINCT OBJECT_NAME(sp.[object_id]) AS TableWithDroppedColsFROM sys.system_internals_partition_columns sipc1 WITH (NOLOCK)INNER JOIN sys.system_internals_partition_columns sipc2 WITH (NOLOCK)       ON sipc1.[partition_id] = sipc2.[partition_id] AND              sipc1.leaf_offset = sipc2.leaf_offset AND              sipc1.leaf_offset < 0 AND               sipc1.is_dropped < sipc2.is_droppedINNER JOIN sys.partitions sp WITH (NOLOCK) ON sipc1.[partition_id] = sp.[partition_id];

Important This script should be used only for the purpose and scope of this article.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 3120595 - Last Review: 05/31/2016 06:19:00 - Revision: 20.0

Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2014 Service Pack 1, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Standard

  • kbqfe kbfix kbexpertiseinter kbsurveynew KB3120595
Feedback