I wrote recently about overcoming an issue with orphaned documents when doing a database attach upgrade from SharePoint 2007 to SharePoint 2010. During that same database upgrade, I managed to run into a second nasty error. After running the PowerShell mount-spContentDatabase command, the progress would go to approximately 15% complete, and then the process would fail. Investigation of the upgrade log revealed the following errors:
[powershell] [SPContentDatabaseSequence] [ERROR] [8/26/2011 8:56:01 AM]: Action 126.96.36.199 of Microsoft.SharePoint.Upgrade.SPContentDatabaseSequence failed. [powershell] [SPContentDatabaseSequence] [ERROR] [8/26/2011 8:56:01 AM]: Exception: The statistics '_dta_stat_1365579903_2_3_4_188_190_27_28_187_192' is dependent on column 'tp_ContentType'. The statistics '_dta_stat_1365579903_28_192_190_4_27_187_188_3_26_2' is dependent on column 'tp_ContentType'. The statistics '_dta_stat_1365579903_3_4_188_190_27_28_187' is dependent on column 'tp_ContentType'. ALTER TABLE DROP COLUMN tp_ContentType failed because one or more objects access this column.
This was happening when the upgrade process was altering the Database schema, and removing the tp_ContentType column from the table. I know SQL quite well from a BI perspective, and well enough from a SharePoint perspective, but I had never before bumped into a problem with SQL statistics.
Statistics are created in conjunction with indices to help optimize performance. Kim Tripp talks about them in a good article here, and Idera has a nice in-depth discussion on them here. However nice they may be, they were preventing my upgrade, so how important were they?
Well, as it turns out, a well meaning SQL DBA had decided that it would be a good idea to optimize the SharePoint databases, and had run a process that created a number of these statistics. Unfortunately, these ones implemented referential integrity constraints that were preventing the schema to be changed, and causing the upgrade to fail.
Luckily, these statistics followed a consistent naming convention. I was fairly easily able to go through each table in the content database and remove the offending statistics (in my case, all of the ones that began with “_dta”. There were other statistics in there, and they were left alone.
Once the statistics were removed, the upgrade proceeded smoothly. Once again, I was forced to manipulate the content database directly to solve a problem, which is not at all a good idea generally. However, in this case, it was done to undo some other changes previously implemented.
Hopefully this can save someone else some grief in the future.2 Comments