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 4.0.2.0 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.
Great post John this was exactly the problem I’m running into. What method did you use to remove the statistics? Did you just delete them in management studio or use Transact-SQL? Not being as familiar with SQL I want to make sure I do this as correctly as possible.
I just used Transact SQL. Delete From ……. etc