More info on SQL Server 2005 SP3 + Cumulative Updates

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!

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)

9 Responses

  1. RP says:

    Microsoft has released SP3 CU#12 this month.
    http://support.microsoft.com/kb/2345449
    Does this have all the cumulative patches including CU#10 and CU#11, that is discussed in this article. Please advice.

  2. Jerry Glen says:

    Excellent Excellent article. This is the first page that actually makes sense of it… Why does microsoft have to be so damn cryptic.
    It takes sharp people like you to sort it out for people like me. Sincerely THANK YOU

  3. Cynthia Dyer says:

    I installed SQL Server 2005 SP3, and now one of my servers has MS Update insisting it needs to install SP1! Any thoughts on why the heck that would happen?

  4. Simon says:

    Great post. Thanks

  5. Evaldo Sobral says:

    I appreciate that information. Thanks a lot.

  6. Ted Henderson says:

    Great article.  We've spent way too much time trying to determine whether cumulative update 10 and 11 are in SQL Server 2005 SP3.  It's very confusing and could definitely be better explained in the SQL Server 2005 SP3 read me and introduction paragraph.  Even though it's mentioned there, it doesn't clearly explain that applying SQL Server 2005 SP3 will undo SQL Server 2005 SP2 updates 10 and 11.  I hope they explain it as you did in the future.

  7. Sujeev says:

    This is really a very useful information. Thank's for your complete explanation…

  8. Scott Whigham says:

    Great post, Aaron – thanks for sharing.

  9. Uri Dimant says:

    Great info, thanks Aaron