Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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

View products that this article applies to.

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.

↑ Back to the top

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 .

↑ Back to the top

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

↑ Back to the top

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 TableWithDroppedCols
FROM 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_dropped
INNER 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.

↑ Back to the top

Status

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

↑ Back to the top

Keywords: kb, kbqfe, kbfix, kbnotautohotfix, kbexpertiseinter, kbsurveynew

↑ Back to the top

Article Info
Article ID : 3120595
Revision : 1
Created on : 1/7/2017
Published on : 5/31/2016
Exists online : False
Views : 430