May 31, 2012 at 3:15 pm
I am working with an existing MS SQL 2008 R2 Standard instance (10.50.2500) running on a Windows Server 2008 x64 Enterprise active/passive cluster. I have a few maintenance plans set up, and was alerted that they were failing to run. These maintenance plans have been set up and running successfully for over a year. The maintenance plan jobs started failing after a routine fail-over for security patching, but the errors have followed back to the other node on subsequent fail-overs.
The SQL Agent job history for the plans shows this error:
Could not load package "Maintenance Plans\[MaintenancePlanName]" because of error 0xC0014049. Description: Cannot find folder "Maintenance Plans\[MaintenancePlanName]".
I am also unable to create a new maintenance plan (through the SSMS wizard or TSQL), failing with this error:
Exception has been thrown by the target of an invocation. (mscorlib)
Cannot find folder "\Maintenance Plans".
I also cannot see any of the existing maintenance plans under Maintenance Plans in SSMS, and executing SP_Help_maintenance_Plan displays only 1 row: "All ad-hoc plans".
I've encountered this error logged in as both local SA and a domain account in the SA server role.
SQL jobs aside from those that ran maintenance plans are running normally. I am not aware of any changes made to cluster or SQL instance other than monthly Windows patches, and so far Google and other forums have not provided me with a resolution. Any thoughts?
June 2, 2012 at 7:00 pm
Do the maintenance plans show up in msdb.dbo.sysssispackages? do the subplans show up in msdb.dbo.sysmaintplan_subplans?
What is the output of this query?:
Use msdb;
Select SP.name, SP.folderid, SPF.parentfolderid, SPF.foldername
From dbo.sysssispackages SP
Left Join dbo.sysssispackagefolders SPF On SP.folderid = SPF.folderid
Where SP.packagetype = 6;
Is SSIS running on the machine (not required any more for running maintenance plans, but gives us another avenue for troubleshooting)? If so, connect to the SSIS instance and see if you can see the folder and plans under Stored Packages -> msdb -> Maintenance Plans
June 6, 2012 at 7:14 am
I don't see any of the maintenance plans in msdb.dbo.sysssispackages, but I can see the subplans in msdb.dbo.sysmaintplan_subplans.
The output of the query against msdb shows only an agent job not related to any of the maintenance plans.
SSIS is running; the Maintenance Plans folder does not exist under MSDB. All of the user-created folders/packages are visible.
June 6, 2012 at 9:06 am
I would consider finding a backup of msdb from before it got deleted and restore it.
June 6, 2012 at 9:35 am
That's an option, but could be rough as quite a few jobs have been added/altered since the issue began. Is it possible to manually recreate the Maintenance Plans folder and build new maintenance plans?
June 6, 2012 at 9:39 am
Doing that is unsupproted and undocumented You could try, but I don't think anyone has done so before. Restore msdb with a different name and insert the missing reacords into the table and see if it works.
June 6, 2012 at 12:36 pm
Not something I want to try on a production box then. So you're recommending that I try reinserting the missing Maintenance Plans folder row into sysssispackagefolders?
June 6, 2012 at 12:55 pm
Yes. Good luck.
June 6, 2012 at 1:09 pm
That worked like a charm. Thank you, it is much appreciated.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply