Upgrading, installing an AddOn, or adding a custom property to a class may hang or fail when using SQL Server 2012 or later, with IBM FileNet Content Manager

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 ('

')  and  index_id in (0,1) and partition_number =1 Substitute the CE table name for '' in the statement. For example 'DocVersion', 'Generic', 'Container', etc. If the output of the statement exceeds 8060, then the table may experience this issue. The table still might not experience the issue when the output exceeds 8060 if the table previously had columns added that were subsequently removed. For an administrator, this means that a CE class had property definitions added, that were subsequently removed. The reason the issue may not occur in this scenario is because when a column is added and then dropped, the select statement above will overcount the bytes until an "alter table rebuild" is executed. It is only when the byte count exceeds 8060 after a table rebuild (or one knows for sure no columns were added and dropped) that the issue can be encountered for the activities stated in this technote. Note that it may take a long time to issue an alter table rebuild command when the table has millions of rows, depending on the table. For example if there is a clustered index on the table, as there is with DocVersion, Container, Relationship and some others, the rebuild will include the clustered index also. A table rebuild can however be performed as an online operation, allowing it to run in the background, but taking a longer time. Note that a table that has less than 8060 bytes might also experience the issue if a succession of property definitions are added to a class all at one time such that a column will be added to a table after 8060 bytes is reached. For example if multiple properties definitions are added during upgrade, installing an add-on, metadata authoring, using Deployment Manager or when deploying a solution with Case Manager, the issue may still be encountered if the sum of the newly added column allocation sizes results in the table exceeding 8060 bytes. Therefore for object stores with underlying tables storing more than a few thousand rows, it is highly recommended to apply the SQL Server fix for this issue to prevent future issues. Without the SQL Server fix, the only workarounds are (a) Allocating a large amount of transaction log disk space and allowing a great amount of time for the ALTER TABLE statement to complete (potentially longer than 24 hours) (b) Staying on a version of SQL Server prior to SQL Server 2012, such as SQL Server 2008, which does not have this issue, or (c) If there are unused properties in the class, and the data is not needed anymore, consider dropping the property definition from the class, if that will free up enough space to reduce the column allocation size below 8060 bytes. However, with option (c), be sure to issue an "alter table rebuild" command because otherwise SQL Server will still internally overcount the bytes and the issue will still be encountered.