Service Pack 2a?

Well, I'm certainly glad I didn't bet that 3050 would be the last we'd hear about SP2.  It's like alphabet soup, I think 3152 (found at http://support.microsoft.com/kb/933097/ – Thanks Chris!) could unofficially be called SP2c or SP2d now?

What an absolute mess.

<…previously in this post…>

Sorry, last update on SP2/SP2a/SP2b, I'm sure.  But I wouldn't put money on it.  What a fiasco this has turned into!

The newest full SP2 download, obtained 2007-03-05 onward, when installed, yields @@VERSION = 9.00.3042.00, just like the previous one (the updated Service Pack includes newer DLLs for maintenance plans, so it shouldn't affect @@VERSION).  You can install the post-SP2 hotfix to get to 9.00.3050.00, but I'm not sure what you get for those 8 extra build increments — waiting for official word.

If you don't know when you downloaded SP2, you can check the version of the file SQLServer2005SP2-KB921896-x86-ENU.exe (for 32-bit Windows, at least).  It should be 9.0.3042.1.  If the version is lower, I suggest re-downloading from http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a — a much cleaner option than installing the old, broken version you have, and then applying an 18 MB hotfix.

In any case, if you haven't yet downloaded/installed SP2, you may want to hold on until all the dust settles.  My guess is that there will be a Service Pack 2b before long, because of all of the problems around maintenance plans that keep getting discovered.  Here is one a colleague ran into:
http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262122

<…previously in this post…>

If you downloaded SP2 prior to 2007-03-05, installing it will yield @@VERSION = 9.00.3042.00.  If you install the hotfix, you will be up to 9.00.3050.00.

The hotfix is described in http://support.microsoft.com/kb/933508 and can be obtained at this URL:
http://www.microsoft.com/downloads/details.aspx?FamilyId=e2c358a1-ecc4-4c49-8f65-daa6b7800eec

<…previously in this post…>

Microsoft quietly posted an update to Service Pack 2.  Note that the date/time and build number have changed to 2007-03-05 and 9.00.3042.01 (according to the page) respectively.  So, if you have installed SP2 or are about to, you might want to grab the most up-to-date bits:

http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en

This sounds a lot like the SP3/SP3a debacle we had with SQL Server 2000.

So, what is different?  Sources say that they have fixed the Maintenance Plan problem they introduced with the initial release of Service Pack 2 — you know, the one where in cleanup tasks, days were interpreted as hours, or hours were interpreted as minutes, or something like that.  So if you had a maintenance plan to do a full backup every hour, and transaction log backups every minute, and a cleanup task to delete all transaction logs more than 12 hours old, then every hour at about 14 minutes after the backup, the cleanup task was breaking the log chain by deleting all log backups more than 12 minutes old (which would include log backups *after* the last full backup!).  This seems like something that should have been caught in QA and regression testing, IMHO.  However, given events over the past couple of days, I'm the last person to start shooting my mouth off about proper testing.  🙂

 

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. :-)

17 Responses

  1. antstanley says:

    Check out this link… its lists all the latest updates and fixes to SQL2005, SQL2000, and SQL7.0; with links … the last SQL7.0 update was 2006, but there have been SQL2000 updates in Feb 2007… http://sqlserverbuilds.blogspot.com/
    it looks very up to date, with the latest info, including SQL 2005 build 9.00.3152, which I now have installed on 2 development servers (one x86 and one x64)…
    did the SP2 install of 9.00.3142.01 over an SP1 install, then applied the hotfix… went through hunky dory on both machines… The one server (the x86 server) has the full house installed, AS, RS, the works, and everything updated 100%… find the SP and hotfix install far smoother than the SP1 install…

  2. jerryhung says:

    WOW, what a mess for SP2, very typical of Microsoft, sad to say
    At least the build 3152 hotfix fixes the Maint. bug too
    The Maintenance Cleanup task uses an incorrect age group for cleanup operations.
    You're telling me I have to instlal another hotfix on my servers again? ;-P

  3. Chris Wood says:

    To make things even more confusuing have you seen this:- http://support.microsoft.com/kb/933097/
    Cumulative hotfix for SP2 build 3152.

  4. jerryhung says:

    Too bad I can't edit comments
    I just checked my Microsoft.SqlServer.MaintenancePlanTasks.dll on 2 servers I updated
    both show file version 9.0.3043.0, and not the 9.00.3050.00 MS stated
    http://support.microsoft.com/default.aspx/kb/933508
    My SQL 2005 SP1 server shows 9.00.2047.00
    so SP2 did go in, just not the right one? WHY? ….

  5. jerryhung says:

    Mnn, interesting. I am sure I applied the SP2a I just downloaded yesterday (.exe shows version 3042.1)
    But a SELECT @@VERSION on the newly updated server returns
    Microsoft SQL Server 2005 – 9.00.3042.00 (Intel X86)   Feb  9 2007 22:47:07   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
    which doesn't show it as 3050.00
    Good thing I haven't updated SP2 in production servers, gonna wait until this SP2 mess is over

  6. AaronBertrand says:

    It may only reside in GAC.  I wouldn't be too concerned about it.

  7. Chris Wood says:

    Uri,
    What if you cannot find the Microsoft.SqlServer.MaintenancePlanTasksUI.dll ?
    Chris

  8. Uri Dimant says:

    Chris
    At the same path where MaintenancePlanTasks.dll  located (btw this dll also under3050.00  version)

  9. Plamen Ratchev says:

    I can confirm Uri's findings. After installing the GDR update on a couple servers and running SELECT SERVERPROPERTY('ProductVersion'), it returns 9.00.3050.00 (and that is also the version displayed when connected in SSMS).

  10. Chris Wood says:

    I just installed the 3050 hotfix. I can find the Microsoft.SqlServer.MaintenancePlanTasks.dll and it has been updated to 3050. I still cannot find the other dll. The Microsoft.SqlServer.MaintenancePlanTasks.dll is also on my client so I guess we need to apply hotfix 3050 to any SP2 client install too!
    Uri,
    Where should the MaintenancePlanTasksUI.dll be found?
    Chris

  11. Uri Dimant says:

    Aaron, Chris
    I have just installed these hotfix followed by link that Aaron posted. So , I see that
    the output of select @@version has changed to 9.00.3050.00 as well as  version of
    MaintenancePlanTasksUI.dll.
    I am on my way to do testing on Maintenance Plan.
    Thanks

  12. AaronBertrand says:

    They've issued a KB article.
    http://support.microsoft.com/kb/933508
    In it they describe that if you've already installed the "bad" version of SP2, you can download this hotfix:
    http://www.microsoft.com/downloads/details.aspx?FamilyId=e2c358a1-ecc4-4c49-8f65-daa6b7800eec

  13. Chris Wood says:

    Aaron,
    I looked at my server and found Microsoft.SqlServer.MaintenancePlanTasks.dll in the Program Files\Microsoft SQL Server\90\DTS\Tasks folder and it was still 9.00.3042.00
    Where should I find these dll's and why does it look like my DLL did not upgrade? Do I have to upgrade all the components on my SQL2005 instance?
    Thanks
    Chris

  14. AaronBertrand says:

    Take a look at MaintenancePlanTasks.dll and MaintenancePlanTasksUI.dll.  They change from 9.00.3042.00 (SP2) or lower, to 9.00.3043.00 (SP2"a").

  15. Chris Wood says:

    Aaron,
    Do you have any idea how we can tell wether we have the latest version once its applied? As I mentioned I upgraded the Database Services/Integration Services and Client Components and using Select @@version still gives 9.00.3042.00.
    Thanks
    Chris

  16. AaronBertrand says:

    I'm not sure yet, Chris.  Microsoft is still working on the story of patching an existing SP2 installation.  I'm sure there will be a smaller hotfix, but who knows?

  17. Chris Wood says:

    Aaron,
    I downloaded the latest version and updated an instance that already had SP2 installed. I updated the Database/SSIS and Client components only. I cannot tell that it is 3042.01 as it does not show anywhere.
    How can I tell that the instance has been updated? I hope that there will be an individual hotfix rather than have to install the new SP2.
    Chris