SharePoint 2007: Upgrade to SP3 fails with “Transaction log for SSP_Search_DB is full”

During the upgrade of SharePoint 2007 from SP2 to SP3, the “SharePoint Products and Technologies Configuration Wizard” showed the following error message:

clip_image001

This happened at Task 8 during the upgrade of the SQL databases. The error message above was not very helpful:

“The following contains detailed information about the failure: Failed to upgrade SharePoint Products and Technologies”

The upgrade.log in the 12 hive log directory shed some more light on it:

[SearchDatabaseSequence] [ERROR] [10/8/2012 10:47:37 AM]: Action 12.2.508.0 of Microsoft.Office.Server.Search.Upgrade.SearchDatabaseSequence failed.

[SearchDatabaseSequence] [ERROR] [10/8/2012 10:47:37 AM]: The transaction log for database 'SSP_Search_DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

When I looked on the SQL server, I saw that the drive with the SQL transaction logs was running out of disk space. That was strange because the numbers looked like this:

Size of DB (.mdf file): 1.1 GB
Size of Log (.ldf file): 8 GB

So I executed a shrink on the SQL server and the log file was back to 1 MB. I tried the SharePoint upgrade again, but got the same error and the .ldf file was again 8 GB (i.e. nearly 8 times bigger than the database itself). On the MSDN Blog, I found the following:

You may experience the following behavior when upgrading your MOSS2007 environment to SP3:

If the Office  Server Search functionality is configured within SSP, while Configuration Wizard is running after installing the WSS and MOSS2007 bits, the SQL Transaction Log assigned to the Search DB will tremendously increase its size (up to 100 GB in some cases).

So make sure you have enough space on the Drive on SQL Server where the Transaction Log is located.

100 GB? They must be kidding. Anyway, I moved the transaction log to a bigger drive (detach DB, move .ldf file, attach DB) and started the SharePoint upgrade again.

Bingo! Now the upgrade completed successfully!

The transaction log grew to 17GB, that’s 17 times bigger than the DB itself!

I found an explanation here:

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.

Update: You can avoid getting a big transaction log by deleting the search index before the upgrade:

  • In Central Administration, go to SharedServices_SSP
  • Click on “Search Administration”
  • Click on “Reset all crawled content”

After the search index is deleted, do the upgrade to SP3. Once complete, start a full crawl for each content source.

Leave a Reply

Your email address will not be published.