SharePoint 2010 Upgrade Failure - Executing DDL Script

I'm in the process of upgrading our SharePoint 2007 farm to 2010, using the DBAttach method. I created a Server 2008R2 VM, installed SharePoint 2010 and configured the farm with default settings.

I backed up our existing content database and restored it to the new server. Our content database is approximately 150 GB, so it's quite large. I ran Test-SPContentDatabase. This reported some missing features, but indicated that none would cause the upgrade to fail. Since this is a test environment, I decided to go ahead and upgrade the database.

I then executed Mount-SPContentDatabase to upgrade and attach the content database. The process reached 19.69% after an hour and appeared to hang. I was able to log into Central Admin and monitor the process as designed. After several hours of what appeared to be no activity, I ran Sp_Who2 in SQL Management Studio and found that I had two Spids listed as SUSPENDED. One was an INSERT and one was a SELECT. One was blocking the other. SQL was deadlocked. After 13 hours, the upgrade failed.

Reviewing the upgrade log files indicated several issues:

1. The first issue I noticed is that the issues identified by Test-SPContentDatabase (missing features) as not being fatal errors were listed in the log file as "The missing feature may cause upgrade to fail. Please install any solution which contains the feature and restart upgrade if necessary." It would appear that there is disagreement between Test-SPContentDatabase and the actual upgrade process.
2. Secondly, and more importantly, it appears that while executing step 84 of 128, a fatal exception occurred. The following entry was recorded:

Action 4.0.98.0 of Microsoft.SharePoint.Upgrade.SPContentDatabaseSequence failed.

[STSADM] [SPContentDatabaseSequence] [ERROR] [8/25/2010 3:50:59 AM]: Exception: Could not allocate space for object 'dbo.AllDocStreams' in database 'MOSS_WSS_Portal_Content' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Autogrow on the SQL log file was set to 10% and had a max file growth limit.  When the upgrade process began populating the AllDocStreams Content Column, two things happened:

1. The larger the transaction log got, the longer it began taking to autogrow. Once the autogrow process approached two minutes, errors began.
2. Next, the transaction log grew so large that my partition ran out of disk space. 

I believe this is happening because none of the transactions are being committed during the upgrade process; thus allowing a rollback in case of failure. Paradoxically, this very failsafe is actually causing it to fail in my case.

I started with a 140 GB content database and it completely filled my 300 GB partition. When performing the upgrade, you need to plan on having plenty of drive space available. Once I moved the database to a terrabyte partition, the error was resolved. I found that, in order to reliably upgrade a SharePoint 2007 content databases to 2010, you'll need to plan for allowing the content database to triple in size during the upgrade. If your content database is 100 GB, you'll need at least 300 GB for the log files to grow.

Profile Import Hung on Enumerating

Having recently taken over control of our SharePoint Farm from the prior administrator, one of the issues I inherited was that the Profile Import in Central Administration was not functioning correctly.

When viewing the Profile Import page, the following error mesage was displayed in red across the top of the page:

An error has occurred while accessing the SQL Server database or the Office SharePoint Server Search Service. If this is the first time have seen this message, tray again later. If this problem persists, contact your administrator.

The result was that the profile import could not be scheduled, so I was having to run it manually every morning by logging into Central Admin and kicking the job off. The downside to this was that not only was it time consuming, but from time to time the import would hang on enumerating.

The workaround for that was to restart the search service. Sometimes the service would not start and I'd have to kill the process. In extreme cases I would have to reboot the server. Not good.

While researching this issue, I found the answer at The Geek in The Pink. A major hat tip to Pink Geek, as the query posted there solved my issue and restored my Profile Import. The process is as follows:

• You'll need to query the MIPScheduledJob table in the SSP database. This is where the records for the full and incremental import jobs live. If they are missing, like mine were, you'll have to rebuild them.
• These can get deleted by running  stsadm -o deletessptimerjob, which I suspect was done by the prior admin
• To rebuild the jobs, you'll need to retrieve the original GUIDS used to identify the jobs. You can recover these GUIDs by querying the MIPObjects table in the SSP database.
• One record in that table will have a very large XML field that will contain the GUIDS for the User Profile Full Import Job and one GUID for the User Profile Incremental Import Job.
• Use the extracted GUIDs and insert two new records into the MIPScheduledJob table.

Once the job records have been recreated in the SSP database, you can login to Central Administration, go to the Profile Import page and you'll be able to configure and schedule the import jobs as designed.

Once I rebuilt the MIPScheduledJob table, the imports have run fine and I've had no further issues. You can grab the query to rebuild the table at Geek In the Pink.