I’m sure you’ll agree that nothing is more exciting than service packs for service packs, so this week, I’m focusing on Microsoft’s release of a critical update for SQL Server 2005 Service Pack 2 (SP2). Please check the status of your SQL Server 2005 SP2 installation. Microsoft recently announced that SQL Server 2005 SP2 had an “issue,” and as the great bard Shakespeare once said, “An issue by any other name is just a bug.”
The Microsoft article “Microsoft SQL Server 2005 Service Pack 2 issue: Cleanup tasks run at different intervals than intended” describes the problem in detail. According to the article, the SQL Server 2005 release to manufacturing (RTM) version measures cleanup intervals in days, weeks, months, or years. SQL Server 2005 SP2 maintenance plan enhancements let users specify the cleanup interval in hours. This might be a great option, but apparently the change in intervals causes the first version of SQL Server 2005 SP2 to interpret cleanup task intervals differently than the original settings. So you could find that your cleanup tasks are deleting your information earlier than you expect.
In addition, if you mix versions of tools and servers, you can get different interpretations of cleanup task intervals. In the article, Microsoft stresses that SQL Server 2005 RTM, SQL Server 2005 SP1, and the newly updated SQL Server 2005 SP2 are compatible. However, “mixing these versions with the initial release version of SQL Server 2005 SP2 could lead to task intervals being interpreted incorrectly in new and modified tasks.”
Needless to say, deleting data--like, say, a backup--before you intended isn’t the best thing in the world, so you really will want to address this problem ASAP. You can download a hotfix (AKA a general distribution release--GDR) at http://go.microsoft.com/fwlink/?LinkId=85124.
To be clear, this problem doesn’t affect SQL Server 2005 RTM or SQL Server 2005 SP1. In addition, Microsoft has rolled the GDR into SQL Server 2005 SP2 effective March 5, 2007. So the only people who need to install the hotfix are those who downloaded and installed a copy of SQL Server 2005 SP2 before March 5.
Not sure when you downloaded SP2? The Microsoft article I cited above gives detailed instructions for checking versions of several SP2 files to help you determine whether you have the good SP2 or the bad SP2. Personally, I think it’s a bad idea to have two versions of SP2, and to avoid confusion, I’d prefer that Microsoft call the new service pack SP2a or something like that. In addition, note that the version number reported by SQL Server doesn’t change based on which version of SP2 you’re using. In other words, querying @@VERSION of an instance with the good SP2 will return the same value as a server with the bad version of SP2. Personally, I think this was a mistake by Microsoft because I believe it will make it more difficult for novice SQL Server sites to ensure they are using the correct version of the service pack. But heck, I’m just a silly columnist so what do I know? Nevertheless, I can’t stress how important it is to ensure you have the correct version of SP2.
P.S. If you try to install the new SP2 over the old SP2, your entire server will explode with a volcanic flash of light and smoke. Ha ha, just kidding. No server explosions, but the new files won’t be installed, the problem won’t be fixed--and your boss might explode if you lose some important data. You can’t easily uninstall SQL Server 2005 SP2 so you must apply the GDR hotfix if you installed a copy of SP2 that you downloaded before March 5.