SQL Server 2005 Service Pack 2 (SP2) Re-release and post fixes explained

The dust has now settled after the release of SP2 for SQL Server 2005 so I thought I would take some time to explain a few details about the re-release of Service Pack 2 and the fixes that have been posted by Microsoft since this re-release. There is much to digest in this post so at the end of the post I've created a summary to help guide you on the next steps to take:

SP2 Re-release and GDR1

We re-released SQL Server 2005 SP2 on March 5th after discovering an issue with maintenance plans and associated cleanup tasks. If you want the gory details behind what happened and how you would possibly be affected if you downloaded and applied SP2 before March 5th, read the following KB article: https://support.microsoft.com/kb/933508 . The bottom line is if you installed SP2 before March 5th, we have a General Distribution Release (GDR) available at https://www.microsoft.com/downloads/details.aspx?FamilyId=e2c358a1-ecc4-4c49-8f65-daa6b7800eec&displaylang=en. This update is also referred to as a critical update. This GDR fix (I'm going to call this GDR1) was also available via Microsoft Update so you might have already been notified of this update. If you applied SP2 on March 5th or later, you have a version of SP2 that already has this fix (that was what we included in the re-release). The issue here was specific to clean-up tasks for maintenance plans: you might have data that was removed earlier than you expect from the cleanup task.Now before you click this link to apply the fix, read on further...

From a version point of view, SP2 was initially released as version 9.00.3042 (normally I recommend you check the Product Version of SQLSERVR.EXE to check versions).

The re-release of SP2 only affected 2 DLLs for the maintenance plan issue: Microsoft.SqlServer.MaintenancePlanTasks.dll and Microsoft.SqlServer.MaintenancePlanTasksUI.dll. In order to distinguish between the SP2 re-release and the original SP2 release, we modified the ProductVersion property of these 2 DLLs to 9.00.3043. You can find these under the following directory %ProgramFiles%Microsoft SQL Server90DTSTasks. Note as the article states that SQLSERVR.EXE is still 9.00.3042 with the SP2 re-release.

For GDR1, the versions of the DLLs are different. They are version 9.00.3050. This includes the above 2 DLLs and also the "core" files we always update when we ship a new build even if code did not change in that file (this includes SQLSERVR.EXE). So 9.00.3043 and 9.00.3050 are effectively the same version of these above 2 DLLs. If you have either of these versions, you have the fix applied for what is contained in GDR1.

So, if you have not applied SP2, then the download site for SP2 has what you need already. Just apply it from https://www.microsoft.com/downloads/details.aspx?familyid=D07219B2-1E23-49C8-8F0C-63FA18F26D3A&displaylang=en

Once you apply SP2 or have already done so before March 5th, then don't worry right now about the re-release or GDR1. Read on about GDR2...

2nd GDR Fix (GDR2)

So after we re-released SP2 and were already ready to upload GDR1, we discovered another issue with maintenance plans. This one involves a failure to run tasks such as a Check Database Integrity Task. This is obviously an important task you may have for the maintenance of your system, so we knew we needed to get a fix to the community quickly. Therefore, we have posted another GDR (let's call this GDR2) to the web specifically for this issue at https://support.microsoft.com/?kbid=934458

If you apply this GDR2 to your server where you have already applied the re-release of SP2, the original release of SP2, or GDR1, the version for the above 2 DLLs and the core files will become 9.00.3054. If you have applied SP2 (original or re-release), I recommend you apply this specific GDR. It includes the fix for GDR1 and therefore includes the fixes you need for these two maintenance plan issues. Microsoft Update will also notify you to apply this fix at the current time if you apply SP2. Since GDR2 is inclusive and is already posted, you should not get Microsoft Update notifications for GDR1 at the time of this posting.

Hotfixes that did not make it into SP2 and the GDR2 Hotfix

The above information should apply to most customers using SQL Server. However, let's say you were provided a hotfix by Microsoft close to the release of SQL Server 2005 Service Pack 2. It is possible this fix did not get included in SP2. This is normal for every service pack we ship. We always have a window where we have to lock down hotfix changes that make it into a service pack. If you were in this situation and applied SP2, you would no longer have the fix applied. So for these customers, we have released a hotfix package that includes all hotfixes that did not get included in SP2. You can download this hotfix package from https://support.microsoft.com/default.aspx?scid=kb;EN-US;933097. This article includes a list of specific fixes that are included in the build. Only apply this package if you are affected by the hotfixes as documented in the article.

If you do install this build, you will now have a version of the SQL Server installed at 9.00.3152.

So what about the GDR fixes I've mentioned above?. If you install this cumulative hotfix package will have you have those important maintenance plan fixes included?. The answer is yes for GDR1 but no for GDR2. However, we have a solution for you already available.

GDR2 (remember it includes the fix for GDR1) comes with a version of the web that can be applied to the cumulative hotfix package (Let's call this GDR2 Hotfix). The GDR2 Hotfix is available for download at https://support.microsoft.com/kb/934459/. After applying this fix, you will now have version 9.00.3159.

What Should I do Now?

There is much information to chew on here and understand. The visioning can get confusing which is why I wanted to provide you the details behind all of these builds. But by time you get this point in your reading you are only interested in "So what do I do now?", take a look at this summary:

I don't have SQL Server 2005 SP2 yet

  1. Apply SP2 from https://www.microsoft.com/downloads/details.aspx?familyid=D07219B2-1E23-49C8-8F0C-63FA18F26D3A&displaylang=en
  2. Apply GDR2 from https://support.microsoft.com/?kbid=934458 (Microsoft Update will also notify you of this after you applied SP2)
  3. You will now be on version 9.00.3054

I installed SP2 but don't know if I applied this before March 5th

  1. Don't worry about checking when you applied SP2, just go right to GDR2 athttps://support.microsoft.com/?kbid=934458 (Microsoft Update may have already notified you)
  2. You will now be on version 9.00.3054

I have applied SP2 and the GDR1 fix

  1. You should have a version of 9.00.3050
  2. If you do, then apply GDR2 at https://support.microsoft.com/?kbid=934458 (Microsoft Update will also notify you of this)
  3. You will now be on version 9.00.3054

I think I was provided a hotfix that did not make it into SP2 but now I want to install SP2

  1. I hope you already know if you applied a hotfix that falls under this category, but if not you can check the version of SQLSERVR.EXE. If you have a version between 9.0.2214 through build 9.0.2219 then this set of steps applies to you. (DO NOT USE THESE STEPS IF YOU ARE NOT USING THESE BUILDS)
  2. If you are in this situation, first apply SP2. Do not delay in moving to step #3. Do it immediately after applying SP2. This is because if you wait and are subscribed to Microsoft Update you may get notified to apply GDR2 (build 9.00.3054). 9.00.3054 is not the build you want if you are in this scenario.
  3. Now apply the cumulative hotfix package at https://support.microsoft.com/default.aspx?scid=kb;EN-US;933097
  4. Now apply GDR2 Hotfix that applies only to the cumulative hotfix package at https://support.microsoft.com/kb/934459/ (Microsoft Update is smart enough to recognize you need this specific version of the GDR2 fix as well once you have applied build 9.00.3152).
  5. You should now be at version 9.00.3159

So as you can see from this summary, after applying the critical updates you should be on either build 9.00.3054 or 9.00.3159. I hope you find this information helpful and I hope the details and summary will help you decide how to proceed with installation of these fixes.

Bob Ward, Microsoft