Freigeben über

Clarifications on KB 3120595

  • Artikel

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:

  1. You have a DB created in SQL Server 2005.
  2. While in SQL Server 2005, you have dropped variable-length columns at the end of the table definition.
  3. 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).
  4. You later upgrade that DB to SQL Server 2012 or SQL Server 2014.
  5. 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

Comments

  • Anonymous
    April 27, 2016
    Thanks for posting this. When I first read the KB article, the issue sounded rather serious. However, it seems like the impact is actually much smaller.
  • Anonymous
    May 02, 2016
    If I rebuild indexes before upgrading from SQL Server 2005, does that also avoid the problem, or does the index rebuild need to happen on SQL Server 2008 or higher?I ran the KB 3120595 script in a database on SQL Server 2005. The script reported one table that was potentially exposed. I then rebuilt the indexes of that table and ran the script again. That time, the script did not report any tables, so it looks like the index rebuild had the desired effect. However, I suppose the database can return to the risky state if someone now adds or drops columns before we upgrade the database.
    • Anonymous
      May 02, 2016
      Your assertions are correct. You can rebuild while in SQL Server 2005 are with that remove reused offsets, but running the mentioned DDL (drop and create) can re-introduce the behavior that becomes an issue when upgrading to SQL Server 2012 or 2014.