Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Cannot delete or run SQL Maintenance Plans from SQL Management Studio

Posted on 2008-10-14
11
Medium Priority
?
1,411 Views
Last Modified: 2012-05-05
I hope this is somewhat clear. Recent deployment of SQL Server 2008 Standard on a Windows 2003 Server. All mandatory service packs and hotfixes have been applied.

I have 2 issues:

1.      Deleting the initial maintenance plan that was created via the wizard from SSMS to backup the databases.

The error message is:

Exception has been thrown by the target of an invocation. (mscorlib)

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

Ive seen where some can overcome this by deleting the faulty plan id record in the sysmaintplan_subplans, sysmaintplan_log and the sysdtspackages tables. These tables are empty.

Subsequent maintenance plans can be created and deleted. However the original maintenance plan remains and the job cannot be deleted once a second Maintenance Plan is deleted. The job is displayed under SQL Server Agent | Jobs. If you attempt to delete the job from SSMS, a message is displayed that the job does not exist.

2.      Any Maintenance Plan created fails to execute successfully.

The error message is:

Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.

Additional information:
      Job jobname.Subplan_1 failed (SqlManagerUI)

The job history log only states the job failed and the last step to run was step 1 (subplan_1).

This message occurs for any Maintenance Plan job defined.

Any guidance would be appreciated. Thank you.
0
Comment
Question by:DrewBryant1961
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 1

Expert Comment

by:Soccerman55
ID: 22811208
This was the solution in SQL 2005, I would assume it would be pretty close to the same for SQL 2008

Execute these in the MSDB database
SELECT * FROM sysdtspackages90 (to get the Plan_ID of the Bad Plan)
DELETE FROM sysmaintplan_log WHERE plan_id = BadPlanID
DELETE FROM sysmaintplan_subplans WHERE plan_id = BadPlanID
DELETE FROM sysdtspackages90 WHERE plan_id = BadPlanID

After doing this, the plan goes away by itself, and the associated job can be deleted w/o error.
0
 

Author Comment

by:DrewBryant1961
ID: 22813949
Thanks for your reply. I saw that, however there is not a sysdtspackages90 table. I removed the entry in the sysdtspackages table. The other sysmaintplan tables have nothing in them.

The job went away although the original maintenance plan remains and cannot be deleted.

Any new maintenance plans created from the wizard will not execute successfully although new plans can be created and deleted.

0
 
LVL 1

Expert Comment

by:radersolutions
ID: 22924773
I'm having the same issue #2 here, on Server 2008 x64 with SQL 2008. Any ideas?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 22927339
Have you looked through the sysjobs tables ?
0
 
LVL 1

Expert Comment

by:radersolutions
ID: 22930770
I just took a look, and didn't see anything useful. The jobs that were created by log shipping work fine, but the maintanance plan jobs fail every time. The only text available says that they failed, with no reason.
I get lines like this:
The job failed.  The Job was invoked by User MYDOMAIN\Administrator.  The last step to run was step 1 (backup_all_DBs).
0
 
LVL 1

Accepted Solution

by:
radersolutions earned 2000 total points
ID: 22930931
I did some more googling, and I've figured it out:

I wasn't aware that SQL Server Integration Services was required for Maintanance Plans to work. Installed SSIS, and then they started working.

thanks!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22931012
About to suggest exactly that - well, first to see if 2005 was still installed, then uninstall (via control panel) and re-install SSIS for 2008 / VS...

Beat me by seconds... Was typing up when your e-mail notice came in.
0
 
LVL 1

Expert Comment

by:radersolutions
ID: 22931107
hehe.. Thanks for your help though. Your first comment got me to thinking that there was definitely something missing based on the fact that the error messages didn't have any real "errors" in them, like something was missing.

This is a fresh installation of 2008, no 2005 pre-existing.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22931801
Pleased you are OK, but what about : DrewBryant1961 ?
0
 

Author Comment

by:DrewBryant1961
ID: 22934474
Installed SQL Server Integration Services and new maintenance plans run fine.

The original plan will not run and cannot be deleted. I get the same exception error listed in issue 1 above.

This was a new install of SQL 2008 and I, too, wasn't aware that SQL Server Integration Services was required for Maintenance Plans to work.

Thanks for your replies.
0
 

Author Closing Comment

by:DrewBryant1961
ID: 31506168
Thanks for your help.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question