Skip to content

Month: August 2011

Upgrade Failure – The statistics ‘xxxx’ is dependent on column ‘tp_ContentType’

Published August 31, 2011

 

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.

2 Comments

SharePoint Upgrade Failure – Orphaned Documents not Orphaned Lists

Published August 31, 2011

Upgrading to SharePoint 2010 from 2007 is well worthwhile, and is significantly easier than the upgrade from 2003 to 2007 was. With that said, there are a number of things to look out for, and a number of bumps along the way. Running the stsadm –o preupgradecheck on your SharePoint 2007 farm identifies a number of the potential pitfalls and alerts you to their dangers.

Recently, I was at a client site doing a database attach upgrade, and we ran into a warning about orphaned objects. Orphaned objects will cause the upgrade to fail, so it needs to be remedied prior to the upgrade.

Orphaned objects are items that exist in the database, but aren’t properly connected to anything in the site collection. Orphaned objects are typically sites or lists. Joel Oleson has a good article on deleting orphaned sites, Orphaned lists are trickier with much of the guidance pointing to removing and then re-adding the content database. Also there is an stsadm command that should repair orphaned objects. The syntax is:

stsadm –o databaserepair –url yoururl –databasename contentDB –deletecorruption

Using the –deletecorruption directive goes ahead and fixes the problem, without it, it just tells you where the problem lies.

Unfortunately, nothing worked in my case. Running databaserepair simply resulted in a return that looked like the following:

<OrphanedObjects Count="1">
  <Orphan Type="SPList" Id="{787A6375-ABA3-4475-AE64-230853EB4448}" SiteId="{13AB0F9E-386B-4128-916C-E70BFC6A45F3}" />
</OrphanedObjects>

This discussion (in which the response marked as an answer isn’t the answer) got me pointed in the right direction. I am loathe to do anything directly in the content database, but if I had an orphan, I could at least find it there. I therefore opened up the AllLists table in the content database and did a SQL search for my list, like:

select * from AllLists Where tp_ID=’787A6375-ABA3-4475-AE64-230853EB4448’

Unfortunately, I got no results. This was baffling.

I had the GUID of the offending list, but it didn’t exist in the list table. I started rooting around elsewhere. It wasn’t too long before I found a reference to it in the AllDocs table, in the ListId column. To me, that meant that I didn’t have an orphaned list, but an orphaned document that was referring to a non-existent list..

After determining that the document was in fact disposable, I deleted its record with some simple SQL

DELETE FROM AllDocs Where ListID = ‘787A6375-ABA3-4475-AE64-230853EB4448’

Once that was done, I ran the preupgrade check, and all was clear – no more orphaned objects. We could proceed to the next roadblock… (more on that later).

This solution worked in my case, but as it involves monkeying with the content database, use at your own risk, and whatever you do – have a backup of the database available!

2 Comments

Strong Passwords Are A Bad Idea

Published August 10, 2011

I’ve always been bothered by ridiculous password policies in many organizations. Frankly, they strike me the same way that many aspects of airport security strike me, which is to say that they are there to give you the semblance of security, but are in fact ineffective, and get in the way of doing something productive.

Mike Fitzmaurice, a friend of mine posted the following cartoon on his Facebook wall this morning, which I think really puts this into perspective.

password_strength

This is taken from the humour site, xhcd.

The problem comes from policies that are developed without a true understanding of the underlying technology. This gets compounded with policies that require users to change their passwords periodically. So what happens when you are confronted with a situation like this? You take this password that is difficult to remember (and in some cases difficult to type) and you write it down on a piece of paper. No amount of instruction will prevent users from doing this – they’re really being forced to do this.

Some time ago I attended a security seminar where a representative from CSIS stated that 90% of all passwords are stored on paper within 5 feet of the computer that they access. The problem isn’t the strength of the password, it’s the strength of the user’s memory.

To me, these Byzantine security policies only serve to defeat their own purpose.  Here’s my policy – choose a good password that you can remember, don’t write it down, don’t tell it to anyone, and unless compromised, never change it. I would urge many IT organizations to adopt their policies accordingly. Let’s not forget that the purpose of information technology is not to impose policies, but to support productivity.

Leave a Comment