Cannot save my Maintenance plan

I use sa to login to sql 2005. I can save Maintenance plan using wizard, however, when I try to save maintenance plan using the maintenance plan design screen. I received error message "Apply to target server failed for job ...". When I check the job, there is no step in the job.

In the Maintenance wizard, there is no maintenance clean up task. I want to create a task to delete my backup file if it is older than certain days.

Any suggestions are welcome. Thanks in advance.

Hi

Dont use the wizard.

You can use the cleanup maintenacne task to cleanup backups files older than x days.

Cheers

|||

Only the wizard let me save my maintenance plan. I cannot save my maintenance plan if I use the design screen.

There is no Maintenance clean up task in the wizard.

|||

What error do you get when you save from designer ?

Gops Dwarak

|||

Oh I missed to see the error message in your mail.

This is the difference in behavior i noticed when created though wizard and through Designer

When you create maintenance plan with a name of job that is already existing through wizard , maintenance plan gets created successfully with a step added to the existing job. Expected behavior should have been to throw valid error saying "Job with this name already exists". When similar scenario is tried from Designer then error is thrown mentioning "Create failed for job 'job name' ". I have opened a bug to be fixed for next release.

Can you check whether job with plan name exists before creating the plan through designer ?

Also, this issue looks like more of SQL Server Agent issue. Can you look at the agent log file(through log viewer) when you try to perform this operation and see what message is logged in there. That may help to narrow down the issue

|||

I checked my sql server log and agent log. Both had no error message recorded.

I noticed if there is job name existed, the Designer thrown error 'Create failed for job...'. I manually deleted the job and maintenance and created from fresh. I got error message 'Apply to target server failed for job 'job name''. Acturally the maintenance plan is saved and job is saved. However, there is no job steps in the job.

I don't think it is SQL agent issue since I have run the job yet. The error occurred at the save process. I guess maybe a security issue. I am using sa to login and sa is dbo of msdb. I can use the wizard to save with no problem.

Thanks for any suggestions.

|||Any suggestions? Thanks.|||

Hi renching

I am afraid i canoot help you much more.

My experience with the new maintenance plans are that they suck.

When i try upgrade 2000 plans to 2005 they just give me errors, i hate the wizard it is nowhere near as user friendly as easy as in 2000.

Basically all i can advise is to do what i did, redo all plans from scratch in 2005.

But you say you have to use the wizard so i cannot help you there cos the wizard cant do what i want it to do, so i avoid it.

As far as exisitng jobs are concerned , i know i had to use sp_delete_maintenance_plan and sp_delete_maintenance_plan_job to properly clear out exisitng jobs and plans.\

Good luck.

Thanx

|||

Thanks. I wish the error message gave more information. Our application is built on SQL 2005. We have no conversions. All jobs are new. I got a lot problem with SQL 2005.

Thanks anyway. I hope someone will help me out here.

|||My SSIS is installed, however, it is disabled. Will this cause the problem?|||Try logging to SQL Server Management Studio on the local server as 'sa' with a server name of '.', then create, schedule, and save the new maintenence plan.|||

renching wrote:

My SSIS is installed, however, it is disabled. Will this cause the problem?

We shouldn't have a dependency on the SSIS service. Try installing SQL Server 2005 Service Pack 2.

http://www.microsoft.com/sql/sp2.mspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/