Content Database Growth on DB Attach Migration from WSS3 to SharePoint Foundation 2010

Introduction

I’ve done a lot of SharePoint Services 3 to Foundation 2010 migrations now and one thing I’ve learnt is that they all have to be treated individually on their merits.  A recent one was a move from WSS 3 on SBS 2003 using SQL 2005 Embedded Edition to a SharePoint Foundation as part of SBS 2011 Standard Edition using SQL 2008R2 Express Edition.

The WSS 3 had a single site collection in a single content database.  I was a little concerned that this database was 7.2GB and I’d have a 10GB limit on SQL Express that is on the Foundation Server.  However I thought I had plenty of spare space for this to work.

I did the usual steps to complete the Farm configuration on the new SBS box and applied WSS Service Pack 2 to the old SBS 2003 box.  The Pre Upgrade Check in STSADM gives you a special warning to let you know that you have a content database that is over 4GB and you might have to do something when you migrate.  Your options are along the lines of:

 

  1. Deploy SQL Workgroup or above and use that for the content database
  2. Remove content from the database before upgrading
  3. Use a time machine to return to point of installation and change the design to use multiple site collections in separate databases
  4.  Hope that it will fit following the upgrade

 Option 4 may be an attractive one but what if we use a SQL Standard in a test machine to trial the upgrade before we attempt to attach to the live SBS 2011 Server.

 Trial Upgrade

I restored the database to a test machine running SharePoint Foundation 2010 using a SQL Standard and used STSADM –o addcontentdatabase.  I didn’t pre-allocate any space to the database or the log so that I could see the files grow.  The database was set to Simple Logging

The STSADM starts counting its percentages and then stopped at 19.69% going no further.  A check of the upgrade.log file shows that it ends with:

[STSADM] [UpgradeAllDocStreamsAndAllDocVersions (4.0.98.0)] [DEBUG] [5/14/2012 10:13:33 PM]: Populating AllDocStreams Content Column
[STSADM] [SPContentDatabaseSequence] [DEBUG] [5/14/2012 10:13:33 PM]: Executing SQL DDL Script.

SQL is still busy, so I use SQL Activity Monitor to see what it’s doing.   I can see one process of interest on my Content Database that is executing the following:

            INSERT INTO AllDocStreams (
                Id,
                SiteId,
                InternalVersion,
                Content,
                Size,
                RbsId)
            SELECT
                ADV.Id,
                ADV.SiteId,
                ADV.InternalVersion,
        ADV.Content,
              ADV.Size,
                ADV.RbsId
            FROM
                AllDocVersions as ADV
            WITH
                (TABLOCK)

Meanwhile the Log file and the database file both start growing and keep growing until the log file is 5.3GB and the database is 11.7GB.  This happens as the result of a single SQL transaction so, even on simple logging, the transaction log will grow excessively.  You can of course shrink the log afterwards.  If you try this on the live site with a 10GB limit the upgrade will fail on allocating space when the database file gets to 10GB.

Conclusion

In this case the customer archived old data out of the content database and we disabled the Recycle Bin to reclaim space.  The database could then be shrunk to 5.5 GB and upgraded to 9GB.  We now just need to deal with the limited capacity for growth in the content database, could really use that time machine.

Advertisements
This entry was posted in SharePoint 2010, SQL Server and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s