…sometimes it tests me…

We have to install SP2 for ConfigMgr before we can start our Windows 7 imaging. The customer we’re working on has SP1 deployed, the site was originally upgraded from SMS 2003. Suffice to say that the SQL database is in a bit of a mess.

Problem 1.

The installation bombs out with an error in the C:\ConfigMgrSetup.log:

The login already has an account under a different user name.

This turns out to be an issue where we have a SQL account with the NetBIOS name of the server but a login id of the full domain machine account. E.g. The SQL User Name is “SERVER” but the login ID is DOMAIN\SERVER$.

ConfigMgr attempts to add its computer account account to SQL (which is also DOMAIN\Server$) and gets the above error. Deleting the existing NetBIOS name account isn’t possible as it owns the “SMS Admins” SQL schema.

The solution is to grant another account (NETWORKSERVICE seems to work) the ownership of the SMS Admins schema, remove ownership for the NetBIOS named account, then you can delete it and once the account is re-added as DOMAIN\SERVER$ you can transfer the ownership of the SMS Admins schema back to this account.

This was, however, just the start of the problems.

Problem 2.

Error in Setup log:

Cannot create a row of size 8065 which is greater than the allowable maximum of 8060.

I still haven’t got to the bottom of this one except that compressing the database seems to make it go away… At the same time as we were having these problems, our DB was also growing by around 200MB per hour, there’s some discussion here that this may be related to a post SP2 hotfix, but I have not been able to confirm this.

Problem 3

Our database is corrupt. And unfortunately it’s in quite an important table…

Error in the setup log:

The Database ID 58, Page (1:140044), slot 20 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.

Running DBCC Checktable tells us that the data is unrecoverable and that only running DBCC CheckDB with REPAIR_ALLOW_DATA_LOSS is going to fix it. After scrabbling around for a while without joy we ran this and it scrapped around 100 rows from the database. Unfortunately the data is in the CI_ConfigurationItems table. There’s a lot of other tables which depend on this, referential integrity issues await!

Problem 3.

Having “repaired” the database we now essentially have a bunch of orphaned items scattered around the Configuration Manager SQL database. This is a real problem as the SP2 setup routine is going to recreate all of the SQL Foreign Key links and if there’s incompatible data in the linked tables the setup routine will bomb out:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Update_ComplianceStatus_CI_UpdateCIs_FK". The conflict occurred in database "SMS_XXX", table "dbo.CI_UpdateCIs", column ‘CI_ID’.

The table will vary, but essentially what this is telling us is that there are records in the Update_ComplianceStatus table that do not have a corresponding record in the CI_ConfigurationItems table, because they were corrupt and have now been removed.

To identify the offending records I executed the following SQL Query

Select distinct CI_ID
From Update_ComplianceStatus
Where CI_ID not in (select CI_ID from CI_ConfigurationItems)

This results in a few records being returned which represent tens of thousands of status messages, inventory records, whatever… They need to be removed:

Delete from Update_ComplianceStatus where CI_ID = ’9420′
Delete from Update_ComplianceStatus where CI_ID = ’9644′
Delete from Update_ComplianceStatus where CI_ID = ’11666′
Delete from Update_ComplianceStatus where CI_ID = ’25041′
Delete from Update_ComplianceStatus where CI_ID = ’26261′
Delete from Update_ComplianceStatus where CI_ID = ’29627′
Delete from Update_ComplianceStatus where CI_ID = ’31677′
Delete from Update_ComplianceStatus where CI_ID = ’33272′

I created the above statements in Excel using Concatenate to add the text to the ID, I recognise that you can do the whole thing programmatically, but this way you can run them one at a time in the SQL interface:

I like this, as these kind of things make me a little nervous…

Once these tasks are complete I rerun setup and wait for the next failure in the log, then repeat the above. A lot.

It took EIGHT HOURS!!!!! but:

Now I need a pint!