Maintenance Plan Changes in SQL Server 2005 SP2

Question

 

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.

 

Answer 1

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?

Answer 2

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?

Answer 3

I have the same error.  Does anybody out there have an answer to this? Please email me a [e-mail]

Answer 4

There is new post sp2 fix, check kb934458.

Answer 5

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”

 

 

Answer 6

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.)

 

 

Answer 7

Jeff, that's what I did. But why can't Microsoft look at sql2k's maintenance plan when design sql2k5's?

Answer 8

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?

Answer 9

I recently, was very much helped by this website: http://www.articles.maindevice.com/ or http://www.maindevice.com/
Here many technical articles.

How you think it interesting article? - " Does a Smartphone Beat a Laptop "
HERE: http: // www.articles.maindevice.com/mobile_art_10.htm

Answer 10

I am having the same problem.  None of the suggested fixes have worked.  After installing SP3 I can no longer access Maintenance Plans through the Database Engine.  I can see them if I connect to the Integration Services engine, under MSDB --> Maintenance Plans.  However, I can't edit them there.  When attempting to access the Maintenance Plans under Database engine, I get this error:  Invalid column name 'from_msx' and Invalid column name 'has_targets'.  I also now cannot create any new Maintenance Plans, they will not save and I get an error on that as well.  The old Maintenance plans are still running, and they do perform the database backup part of the task but the cleanup fails and the email notification fails.  When is Microsoft going to address this issue? 

Answer 11

Check if the maintenance plans are when you query the DMV , sys.dm_db_maintenance_plans
The error is referencing "master/target servers". Was this SQL ever part of a multi-server administration setup ?
Also did your SP3 patch install successfully ? Check the setup log for database engine (Sqlrun_sql.msp.log) to make sure no errors are reported. I have seen issues where due to a hotfix/patch failure, certain scripts that were supposed to update MSDB objects did not run and this can cause such issues.

Another thing to check is if you can open the maintenance plan, click on the "Connections" button and see which instance is this maintenance plan pointing to. I have again seen cases where due to incorrect connection configuration or when MSDB from another server is restored, old connection strings are still being used.

SP3 as such has no known issues with Maintenance plans. And its been out there for 1 year !
SP2 had a lot of issues with maintenance plans. For people out there experiencing issues with

a) Cleanup tasks not working
b) Old backup files getting deleted @ incorrect intervals
c) SSIS package errors with maintenance plans

Please apply CU6 for SQL 2005 SP2 or better SP3 as most issues you will face with maintenance plans are addressed in these builds.

---

Patience is a Virtue... So is "Delayed IO" :)