Maintenance Plan Changes in SQL Server 2005 SP2

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.

I installed sp2 on my server. It failed on database engine service. And my maintance plans corrupted. I am not able to open existing plan or create new plan.

Gives the following error:

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)

Any help?

|||Good to see cleanup task in maintenance plan. But the task can only delete one type of files. If I want to delete db backup files, log backup files and plan log files, I need to add two more cleanup tasks after creating plan. Why can't add cleanup option in backup and logging page like sql2k's plan does?|||I have the same error. Does anybody out there have an answer to this? Please email me a bkellman@.hcfmw.com|||There is new post sp2 fix, check kb934458.|||

the update to SP2 did not fix it for me.

however this solution in a different blog did: regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll”

|||

Rmiao,

You don't need to create new tasks to perform the cleanup that you want. You can define one type of clean up task in a miantenance plan and then add addtional steps to the plan to clean up other types of files as well. The Wizard does not deal with this very well, expecially since you want to remove several diferent types of files.

Once you have created the Maintenance Plan that you desire, edit the maintenance plan. With this method the plan will appear as a flow chart with all of the steps shows as nodes connected by conditional lines. You are free to add as many addtional tasks as you wish at this point. I further suggest that conditions be added so that previous backup files are not deleted in case a new one is not created for some reason (i.e. full disk drive, missing/bad media, etc.)

|||Jeff, that's what I did. But why can't Microsoft look at sql2k's maintenance plan when design sql2k5's?|||The patch that fixes the maintenance plan bug is supposedly

SQLServer2005-KB933508-x86-ENU.exe, which addresses KB933508. Unfortunately the installer seems to be buggy. When I try to install the patch, I get to a point where I cannot select anything to install from the tree, and the Next button is grey (disabled).

Anyone experience this, or know of another patch that includes this fix that actually installs?