Troubleshooting
Problem
A change was made starting in SQL Server 2012 such that when an ALTER TABLE statement is executed to add a nullable fixed length (non-sparse) column to a table, and the allocation size of the columns including overhead exceeds 8060 bytes, an update is also performed on each row in the table to insert a null value for the new column. The update is done to confirm that the data in each row fits into the pagesize limit of 8060 bytes so that later operations such as inserts or table rebuild do not fail at runtime. Some Content Engine object store tables might exceed this 8060 byte limit, and therefore would be affected by this issue. For tables in large object stores, this update may take hours, or even days, to complete. A fix is now available for this issue. See the section "Resolving the problem" below for details on installing the required SQL Server cumulative update and setting the required SQL Server trace flag. Prior to SQL Server 2012, ALTER TABLE for nullable fixed length columns was "online", meaning that it was a metadata only operation and would execute immediately, regardless of the total allocation size of the columns in the table. In SQL Server 2012 and later, the same operation is "offline", because it requires an update of each row in the table in a single transaction. The consequence of this type of operation however is that ALTER TABLE can take a very long time for a table with a large number of rows, and require very large amounts of transaction log space. IBM FileNet Content Manager customers using SQL Server 2012 or later will potentially be impacted by this issue whenever an Administrator upgrades Content Manager, installs an AddOn, or adds a new property to a class. If any of these operations results in one or more fixed length columns being added to a table, and the table's column allocation size exceeds 8060 bytes, then the issue will be encountered. If the upgrade, AddOn installation or metadata authoring creates more than one new property, the length of time will be a multiple of the time required for each property individually. The affected Content Engine property data types are: Boolean, Date Time, Float, ID, Integer, or Object. String types are not affected because the underlying column is an nvarchar, which is a variable length type. Similarly, binary types are not affected because the underlying column type is image, which holds variable length binary data. It is known already that any IER (IBM Enterprise Records) systems will experience the issue, due to the large number of DocVersion table columns added into the IER object stores. This issue could also happen with Deployment Manager when importing a class definition, and with Case Manager when deploying a solution. To determine if a Content Engine table column allocation size exceeds 8060 bytes, run the following select statement in a query window using SQL Server Management Studio on each Content Engine table that has more than a few thousand rows: select 1+1+2 + 2 + (case when sum (case when leaf_offset < 0 then 1 else 0 end) > 0 then 2 else 0 end) + ( (count (*) + 7)/8 ) + count (case when leaf_offset < 0 then 1 else null end) * 2 + sum( case when max_length=-1 then 24 else max_length end) from sys.system_internals_partition_columns col join sys.partitions par on col.partition_id = par.partition_id where object_id = object_id ('