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.

Comments (18)

  1. In SQL Server 2005 SP2, cleanup interval for the History Cleanup Task or the Maintenance Cleanup Task

  2. Carpe Datum says:

    If you’ve installed Service Pack 2 for SQL Server 2005, you might want to check out the information on

  3. Recently Microsoft has release the much awaited SP2 for SQL Server 2005. Guys go and grab it – it has

  4. Dan's Blog says:

    If you use Maintenance Plans and have upgraded or plan to update to SQL2K5 SP2 you need to read this

  5. Pokud ve svých maintenancech plánech používáte History CleanUp tasks a Maintenance CleanUp tasks, meli byste si precist http://blogs.msdn.com/sqlrem/archive/2007/02/24/Maintenance-Plan-Changes-in-SQL-Server-2005-SP2.aspx, kde jsou popsany zmeny, ktere

  6. James Moore says:

    Thought I better post this incase you havn’t seen it – If you are using SQL Server 2005 maintainance…

  7. A regression bug was introduced in SP2 that resulted in a view problems when used in evironments that

  8. A regression bug was introduced in SP2 that resulted in a view problems when used in evironments that…

  9. Microsoft quietly released an updated version of SQL Server 2005 SP2. The new build number is 9.00.3042.01….

  10. Depuis hier le téléchargement du Service Pack 2 de SQL Server 2005 a été remis à jour (9.00.3042.01).

  11. Alex says:

    Critical Update for SQL Server 2005 Service Pack 2 (KB:933508)

    This update resolves an issue with maintenance plan cleanup tasks intervals.

    http://www.microsoft.com/downloads/details.aspx?FamilyId=e2c358a1-ecc4-4c49-8f65-daa6b7800eec&displaylang=en

  12. Проблема, найденная в Microsoft SQL Server 2005 Service Pack 2, стала основанием для критической заплатки,…

  13. Проблема, найденная в Microsoft SQL Server 2005 Service Pack 2, стала основанием для критической заплатки,…

  14. Another one: http://support.microsoft.com/kb/934459 (KB:934459) that fixes major issues for us.

  15. romeolanuza@verizon.net says:

    had just recently updated system inf.!!!! hoe d i know if i need to update the sysytem management server?????

  16. I feel like there is lot of confusion ( new users ) in deciding which build to apply after the re-release