SharePoint 2007: SSP upgrade issue with SP3

The Office Server Search gatherer crawled documents in batches. It creates a batch ID each time it loads documents from the crawl queue into SQL, these BatchIDs are stored in MSSBatchHistory Table (part of SSP Search DB). There was a limitation that existed in Search where in it does not clean up the MSSBatchHistory table upon crawl completion. As a result, MSSBatchHistory table continues to grow till one day BatchId (as defined as Identity type column) reaches the limit of Int described here , which would cause a failure in Search.

In-order to overcome this situation, Microsoft released a fix to

a) Change the BatchID column type from Int to a BigInt

b) Delete rows from MSSBatchHistory at end of each crawl

 

This fix was released in April 2011 CU (12.0.6557.5001) as described in the following KB articles,

For Windows SharePoint Services 3.0: KB2512780

For Office SharePoint Server 2007: KB2512781

So what's the Problem?

If you are upgrading to the April 2011 CU or Service Pack 3 for WSS 3.0/SharePoint 2007 coming from an older build (say Pre-April 2011 CU), this fix would get applied & as a part of the upgrade it will change the BatchID column from Int to BigInt.

This process can take a lot of time (many hours) depending upon the number of records in the MSSBatchHistory table & will also grow the transaction log for SSP Search database considerably.

Hence it is recommended that you plan this upgrade and monitor it to completion,

Steps to monitor & plan for the upgrade:

1. You can run the following SQL command on the Content DB to look at the Size & number of rows in the table & the output will look like this.

Sp_spaceUsed 'MSSBatchHistory'

2. This is what the upgrade logs generated during the PSConfig execution will show,

[SearchDatabaseSequence] [DEBUG] [12/18/2011 12:11:53 PM]: Calling get SchemaVersion on Database BPA_SSP_Search, Status = Upgrading.

[SPManager] [DEBUG] [12/18/2011 12:11:53 PM]: [SearchSharedDatabase Name=SSP Name Parent=SPDatabaseServiceInstance] Running 1 of 1 steps

[SearchQFE21038DatabaseAction] [12.2.508.0] [DEBUG] [12/18/2011 12:11:53 PM]: Begin Initialize()

[SearchQFE21038DatabaseAction] [12.2.508.0] [DEBUG] [12/18/2011 12:11:53 PM]: End Initialize()

[SearchQFE21038DatabaseAction] [12.2.508.0] [DEBUG] [12/18/2011 12:11:53 PM]: SearchQFE21038DatabaseAction.ShouldRun returns true

[SearchQFE21038DatabaseAction] [12.2.508.0] [DEBUG] [12/18/2011 12:11:53 PM]: Begin Upgrade()

[SearchQFE21038DatabaseAction] [12.2.508.0] [DEBUG] [12/18/2011 12:11:53 PM]: SearchQFE21038DatabaseAction.Upgrade: Changing MSSBatchHistory column BatchID type to bigint on database SQLServrName:SSP_searchDBName

3. Depending on the size of the 'MSSBatchHistory' table and number of rows, plan sufficient downtime.

4. Ensure the SSP Search DB is in Simple Recovery mode. (This is default setting)

5. It is recommended to have sufficient amount of disk space on the drive having the transaction log for the SSP Search DB. You may want to move this to a drive with more space if required before the upgrade.

6. It is recommended to set the MAXDOP =1 on SQL server before upgrade, as suggested here https://blogs.msdn.com/b/rmeure/archive/2011/12/23/optimizing-sql-for-sharepoint.aspx

References:

Estimate how long the upgrade process will take and the amount of space needed (Office SharePoint Server)

https://technet.microsoft.com/en-us/library/cc262891(office.12).aspx

Post By:Rajan Kapoor [MSFT]