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.
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
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:
- Cumulative Update 13 for SQL Server 2014
- Cumulative Update 6 for SQL Server 2014 Service Pack 1
- Cumulative Update 10 for SQL Server 2012 Service Pack 2
- Cumulative Update 2 for SQL Server 2012 Service Pack 3
Note For more information about the latest SQL Server builds, see Where to find information about the latest SQL Server builds .
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
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.
Article ID: 3120595 - Last Review: May 31, 2016 - Revision: 1