Cannot create maintenance plans in cluster 64x sql server 2005 standard - by Anonymous3362

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 307018 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 10/26/2007 5:09:50 PM
Access Restriction Public


We originally had build 9.0.3042 installed on a cluster environment and were able to create maintenance plans.  Integration service was installed and we were able to view the maintenance plans in Integration Services and were able to export the plans.  

When we ran KB921896 to upgrade the build to 9.0.3054 we lost the ability to view the maintenance plans within SQL Server >> Management >> Maintenance Plans.  We attempted to build a new plan.  The wizard appeared.  It created the plan showing the new jobs but the plan still did not show within SQL Server.  Thus we were unable to modify the plan. It did show within the Integrations Services area and we were able to export the plan to a dtsx file.

The only way to remove the jobs was to delete the records found in msdb.sysmaintplan_subplans first.
Sign in to post a comment.
Posted by fisher1st on 11/14/2008 at 3:18 AM
I have the same problem. This is production system so i don't want to reinstall everything from scratch.

Does anyone know something about solving this case ?

Posted by Anonymous3362 on 11/12/2007 at 2:20 PM
We re-installed everything from scratch - made sure that both nodes on the cluster had the exact build for both the cluster components and non-cluster components. We were able to get the second accumulative patch for SQL 2005 SP2 from Microsoft and added that. We have decided not to run KB921897 on top of this because that added additional problems.

We then rebuilt the maintenance plans using the maintenance plan wizard within SQL Server Management Studio. We were then able to export the plans using Integration services. What we noticed however, are the following:

1. The maintenance plans that were exported did not retain the scheduling and notifcation information. We would have to re-open them within SQL, add that information back in and then re-save it.
2. Even though we removed all mention of the connection string within the plan, it still wasn't not completely removed and thus, caused the jobs to fail. (We had built more than one instance within SQL Server and wanted to have the same plans within each instance.) Thus, we found that the Business Intelligence tool was useless and we had to resort to building each plan individually within each instance.
2. If we opened the exported maintenance plans in the Business Intelligence tool and modified it within there, when we exported back into SQL, the plans did not work.
3. We found that if we needed to delete a maintenance plan, it needed to be deleted under the Management >> Maintenance Plan area of SQL Management Studio. If the job was deleted (located under SQL Agent) within SQL or if the plan was deleted from withiin Integration Services, items within the maintenance plan were orphaned.
- If the job was deleted (not the maintenance plan - just the job), the SSIS portion of the maintenance plan was orphaned.
- If the maintenance plan was deleted within Integration Services, the jobs associated with the plan were orphaned and could only be removed by deleting records out of MSDB.

It appears as if the two components of the maintenance plan - SSIS portion and the SQL T-SQL portion of the job (located in MSDB) are not adequately connected together. I would think that the the plan should be deleted in its entirety no matter where we decide to delete it.
Posted by Microsoft on 11/12/2007 at 8:20 AM
Thanks for getting in touch with us regarding your issues with maintenance plans on a cluster. I have assigned this to our test team for investigation. Please update us if you develop any further understanding or workarounds.