Clarifications on KB 3120595
Since the release of Cumulative Update 10 for SQL Server 2012 Service Pack 2, Cumulative Update 2 for SQL Server 2012 Service Pack 3, Cumulative Update 13 for SQL Server 2014 and Cumulative Update 6 for SQL Server 2014 Service Pack 1 earlier this month, there have been questions about the precise chain of events that may lead to the issue described in KB 3120595.
So to further clarify these, I would like to call out that the issue may happen when you have met the following conditions (as now also stated in the KB text) in this order:
- You have a DB created in SQL Server 2005.
- While in SQL Server 2005, you have dropped variable-length columns at the end of the table definition.
- While in SQL Server 2005, you have added new variable-length columns that are of similar data type and size of the previously dropped columns (which may so lead to overlapping offsets).
- You later upgrade that DB to SQL Server 2012 or SQL Server 2014.
- You immediately start doing UPDATE DML operations with overflow data, without applying the workaround or the fix.
So based on the description above, here are a few scenarios that are considered exempt from hitting the issue:
- You have created a DB in SQL Server 2008 or any higher version, and in that DB you have done the same DDL operations in the order defined above.
- You have rebuilt indexes before starting UPDATE operations, on a DB upgraded from SQL Server 2005 to SQL Server 2012 or SQL Server 2014.
- You have applied the fix in KB 3120595 before starting UPDATE operations, on a DB upgraded from SQL Server 2005 to SQL Server 2012 or SQL Server 2014.
- You have upgraded a DB from SQL Server 2005 to SQL Server 2008* or any higher version*, and only then added new variable-length columns, that are of similar data type and size of the previously dropped columns back in SQL Server 2005.
- You have upgraded a DB from SQL Server 2005 to SQL Server 2008* or SQL Server 2008R2*. During the lifetime of the database while in SQL Server 2008 or SQL Server 2008R2, you have rebuilt indexes, before upgrading again to SQL Server 2012 or SQL Server 2014.
* Independent of DB Compatibility staying at 90 (where available) or upgrading to the engine level DB Compatibility.
We also provided a script in the KB article that will output any tables that have dropped columns, which offset matches existing non-dropped columns on the same table. This helps clearly identify which tables in a database may be potentially exposed to the issue, when these drop and create DDL operations were both executed in SQL Server 2005. You can run the provided script in SQL Server 2005 and any later version.
Pedro Lopes (@sqlpto) – Senior Program Manager