While the end date for MOSS 2007 SP2 support is coming closer, most of the them are planning to upgrade to SP3 no sooner than later. It is  extremely important to plan carefully to avoid any problem during the patching process in a large environment.

One of the important update that came in April 2011 CU (before SP3) was cleaning up MSSBatchHistory table in SSP Search Database. This fix, http://support.microsoft.com/kb/2512781, deletes rows from MSSBatchHistory at the end of each crawl. Before the fix was released, you have to manually reset all crawled content in the crawl setting periodically as described in the article, http://support.microsoft.com/kb/948909

In a large environment where search is used extensively, the table MSSBatchHistory in search database is updated every time when a full crawl or an incremental crawl is performed. The MSSBatchHistory table has a column called BatchID of type int. When the BatchID reaches the max of int type 2,147,483,647 (4 bytes), there is a potential risk of arithmetic overflow. This could possibly lead the crawl to hang as the search engine becomes busy and becomes unresponsive to serve any request.  In SP3, a fix was included for changing the column BatchID type int to bigint in the MSSBatchHistory table. This means that now the BatchID column can go upto 9,223,372,036,854,775,807 (8 bytes).  When you install SP3, it runs an Alter query in the MSSBatchHistory table to change the BatchID column type from int to bigint. In the upgrade log, you will find “Changing MSSBatchHistory column BatchID type to bigint on database {SharePoint_SSP_SearchDB}”. Precisely speaking this happens in step 8 of 9 in the configuration wizard.

It is very important to understand that this alter query will go over each row one by one to alter the table schema. In case, the table wasn’t cleaned up for long time, chances are that it could contain over hundred millions of records in a large environment. In that case, the query will keep running for long time until it does for each record in that table.

What if I haven’t installed April 2011 CU and install SP3?

In case, you haven’t cleaned up the MSSBatchHistory periodically by resetting the crawled content, there could be large number of records (~ hundred millions) in MSSBatchHistory table. This could possibly lead to long running query execution when the change from int to bigint in the BatchID column happens. In this case, you might need to reset the crawled content and make sure that the table is cleaned up before installing SP3 patch.

What if I have installed April 2011 CU and then install SP3?

Remember that the April CU fix deletes rows from MSSBatchHistory at the end of each crawl. In case you haven’t run the crawl after April 2011 CU installation, chances are that the MSSBatchHistory can contain large number of records ( ~ hundred millions) in which case the query to change the BatchID column type will run for long time. You need to really make sure that this table contains very few records before installing SP3!!

Is it possible to estimate the time to complete the query (Changing int to bigint type)?

Unfortunately No!! Once the configuration wizard starts and when it executes the script to change the BatchID type (step 8), there is no way to estimate the time remaining or the number of records left to complete. The search database is put into lock mode by PSConfig, so there isn’t much that we can do except to wait for the query to complete successfully and move forward in the patching process.  Even SQL profiler trace won’t give much detail on this.

On average, how much time does this query alone take to complete?

It depends on numerous variables like network speed, SQL disk speed and so on. Based on my experience I’ve seen this particular query (to change BatchID column type) running over couple of days where the table has over hundred million records (table size over ~3 GB). So in MSSBatchHistory table, if you have over hundred millions of records, chances are high that this query might continue to execute for many number of hours or even days.

What will be the growth size of Transactional logs in SQL?

There is no fixed limit on how much the Tlogs can grow, but it can be roughly estimated to grow up to 3x to 4x the size of your search database. Say for example, if your search database is 100 GB, it can grow up to 400 GB. Again, it depends on many factors and the growth of Tlogs can even slow down as it progresses. Make sure that you have enough disk space for Tlogs to grow.

Can we run delete query from SQL to delete the rows in MSSBatchHistory before installing SP3?

No and No. Running any SQL query directly on SharePoint 2007 will fall under unsupported scenario, http://support.microsoft.com/kb/841057.

Make sure that the table MSSBatchHistory has just few records in which case you need not essentially worry about the query execution time as the alter query would run much faster with just few records!!