24 February 2007
Maintenance Plan Changes in SQL Server 2005 SP2
Update (2007-Mar-06): http://blogs.msdn.com/sqlrem/archive/2007/03/06/Maintenance-Plan-Changes-Update.aspx
Users of SQL Server 2005 maintenance plans should be aware of a change in SP2 that affects existing cleanup tasks until updated using SQL Server SP2 tools.
Who is affected?
You are affected if you use SQL Server 2005 maintenance plans, those plans include a History Cleanup Task or a Maintenance Cleanup Task, and you install SQL Server 2005 SP2. Users of SQL Server 2000 legacy maintenance plans are not affected.
What is the issue?
Some maintenance plans might include the Maintenance Cleanup Task and the History Cleanup Task, which allow users to delete information older than a specified interval. When SQL Server 2005 was released, this interval was measured in days, weeks, months, or years. In response to customer feedback, SQL Server 2005 SP2 includes significant enhancements to maintenance plans, including an enhancement that allows users to specify the cleanup interval in hours. After upgrading to SP2, and until you update cleanup tasks using SQL Server SP2 tools as described below, existing cleanup task intervals are misinterpreted. This leads to earlier data cleanup than was intended.
What if I have already installed SP2 on my server?
You can restore your maintenance plans to their former behavior by opening them up in the Maintenance Plan Designer, opening any cleanup tasks, adjusting the age units to the proper value, and saving the maintenance plan. Use SQL Server 2005 SP2 tools to make these changes.
What if I haven't yet installed SP2 on my server?
We are currently investigating our approach to this problem and will provide guidance shortly. If you depend on the Maintenance Cleanup Task or the History Cleanup Task and are not able to verify and possibly update cleanup tasks using matching server and tool versions, you may want to wait for that guidance before installing SP2.
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
Comment Policy: No HTML allowed. URIs and line breaks are converted automatically. Your e–mail address will not show up on any public page.
I am a Program Manager on the SQL Server Manageability Team at Microsoft.
I am a proud University of Michigan alumnus. I graduated with a business degree from the Stephen M. Ross School of Business in 2005.
