More info on SQL Server 2005 SP3 + Cumulative Updates

AaronBertrand — Wed, 17 Dec 2008 17:31:00 GMT

I recently posted about the availability of SQL Server 2005 SP3. I have a bit more information about cumulative updates and the service pack, for those of you who might be confused about the whole mess involving CU#10, CU#11, post-SP3 CU#1, and are wondering whether you need to install some pre-SP3 CUs and then SP3, or SP3 and then the post-SP CU1, or just SP3, or some other combination. I don't really blame anyone for the confusion.

The Problem

The documentation that shipped with SP3 (which you can find online) has a "What's New" page that currently states:

"Microsoft SQL Server 2005 Service Pack 3 (SP3) contains all the cumulative updates for SQL Server 2005, and fixes to issues that have been reported through our customer feedback platforms."

The part about "all the cumulative updates" is incorrect and in the process of being changed (they added a comment to the article but it takes longer to roll out these changes to MSDN properties, never mind the Books Online application itself).

The Bigger Problem

SP3 does NOT include the fixes in CU#10 and CU#11, since they were published after the service pack had begun final testing. The purpose of the post-SP3 CU#1 (which we should see no later than Friday) is to fill that gap and apply those fixes to an SP3 install. When this new cumulative update is available, at least the last time I checked, it will be posted in KB #959195. (Currently this KB yields a 404, and it has already been changed once from #960828, so I wouldn't be surprised if it changes again.)

So, if you want to be up to date, my suggestion is to wait on SP3 until CU#1 is available. If you have CU#10 or CU#11 and you apply SP3, you will LOSE YOUR CHANGES from those CUs. There will be no warning; it will just happen. Do NOT say I didn't warn you!

Long Story Short

Here is a quick summary of what I think you should do. Run the following:

SELECT @@VERSION;

If the version returns < 9.00.3294:

Apply SP3 now, and then apply SP3 CU#1 when it is made available.
This will ensure you will eventually have all of the fixes from CU#10, CU#11 and SP3.

If the version returns >= 9.00.3294:

You should NOT install SP3 until the forthcoming CU#1 is available.
Otherwise you will lose the fixes from CU#10/11 and will not be able to restore them until CU#1 is ready.

Of course maybe you didn't know about CU#10 and CU#11, and they have fixes you want to take advantage of now. In that case, I suggest installing CU#11 (9.00.3301, available in KB #958735) and then following the second point above... wait on SP3 until CU#1 is available. But read the rest of this article first, because there may be a more advantageous fix for you in SP3.

Other Random Notes

This is the challenge when fully testing a service pack before releasing it; there are bound to be issues in the meantime that need to be rushed out to customers, and so you have the potential for gaps on both sides: the less-tested CUs and the more stable but slightly older SPs. In this case we can see that there are fixes in SP3 that weren't in CU#11, and fixes in CU#10 and CU#11 that didn't make it in time to be in SP3. It is an interesting problem to solve without putting all potential fixes on lockdown for the duration of an SP testing cycle. Obviously this approach is not always going to be feasible, and becomes less so over time as SQL Server becomes a more complex product.

On the bright side...

With all of this confusion also comes some nice "surprise" fixes that are in SP3 but were not released in any of the CUs. One of the fixes you get in SP3 that are not included in either CU#10 or CU#11 is a CPU timing issue, which caused errors in the logs such as:

The time stamp counter of CPU on scheduler id x is not synchronized with other CPUs.

...and...

CPU time stamp frequency has changed from x to y ticks per millisecond. The new frequency will be used.

The details of these changes can be found in KB #931279, and have been blogged about in more detail by Bob Dorr of CSS. You can also see some earlier background on the problem, and how it was fixed for good in SQL Server 2008, here and here.

For a lot of people, this fix alone will be worth the trouble. I wonder how many people have seen these error messages in the log, and picked up the phone and blasted their reps at <insert SAN vendor here> because they assumed the error messages about I/O stalls were really indicative of poor hardware performance. I'll admit that I will need to review some of the catalysts over the past couple of years that have made me blame the hardware when the "problem" was merely a fabrication.

Stay On Top Of It

You should keep an eye on the SQL Server Release Services blog over the next few days, as they will be publishing more information about these releases. I am going to be offline for several days and don't want anyone expecting me to keep updating these blog entries every 20 minutes. :-) Happy Holidays!

Search results matching tags 'cumulative updates', 'service pack 3', 'service packs', and 'SQL Server 2005 updates'

More info on SQL Server 2005 SP3 + Cumulative Updates