By: Greg Robidoux | Updated: 2007-03-08 | Comments (8) | Upgrades and Migrations
If you haven't heard already, there was a problem identified with service pack 2 for SQL Server 2005. The problem has to do with the "History Cleanup" tasks and the "Maintenance Cleanup" tasks. The issue is that the interval that these tasks runs is not based on what you think when you set this interval. Based on this, your backup files or other files that are cleaned up by these tasks may be prematurely deleted or these files may stay around longer then intended and therefore possibly cause disk space issues.
If you haven't yet installed service pack 2 for SQL Server 2005 then you won't be impacted. The version that has this issue is 9.00.3042.00.
You can see what version you are running by using SQL Server Management Studio. The version number is displayed next to the instance name. You can also run SELECT @@version to get the version number. Refer to this previous tip to see a list of all versions.
If you have version 9.00.3042.00 installed you should download and apply the general distribution release update. This can be found here Critical Update for SQL Server 2005 Service Pack 2
After the update is applied you should be at version 9.00.3043.00.
The Microsoft KB article also states that you might have to restart the computer after the update.
Also, if you created or edited maintenance plans or Integration Services packages by using the initial release version of SQL Server 2005 SP2, you must verify and update the cleanup task intervals after you apply the update.
To do this, follow these steps:
- Open the maintenance plan or the Integration Services package
- Open each cleanup task
- Adjust the cleanup interval to the correct value
- Save the plan or the package
One last note, this does not affect SQL Server 2005 Express Edition.
- Check to see what version you are running
- Download and apply the update
- Check your maintenance jobs and SSIS packages to correct the problem
- Verify that you are not missing critical files that these jobs create, if you are try to recreate the files if necessary
- Your SQL Server 2005 Express instances are not impacted and therefore nothing needs to be done
Last Updated: 2007-03-08
About the author
View all my tips