Removing a Maintenance plan

Question

Post reply

Removing a Maintenance plan

Reg_Mayfield

SSCrazy

Points: 2661
More actions
June 11, 2007 at 8:01 am

#119403

I recently upgraded to Sql server 2005. We tested Sql Server 2005 on a new machine with the name ServerB. I created a number of Maintenace plans while the box was being configured to look like the Sql 2000 Production server (ServerA). Last Friday night I made a copy of all my Sql 2000 databases and restored them on ServerB. I then shutdown the Sql server 2000 box, removed it from the domain and renamed the Sql Server 2005 box with the name ServerA and then brought online. Everything looked fine and I figured I did not have to run the following commands:
EXEC sp_dropserver '<old_name>'
go
EXEC sp_addserver '<new_name>', 'local'
go
Now when I look at the connection properties for the Maintenance task I created when the server was named ServerB I see that that server is listed as the Local Server connection and is grayed out so that I cannot change it. I tried adding a new connection and then using that connection to delete the Maintenance task but I keep getting the error:
"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 connecting to Sql Server) (Microsoft Sql Server, Error:53)

Would it help to run the stored procedure sp_dropserver and if so can I do it safely as this is the production server and I don't want to do anything during working hours that might jepordize system integrity...
Any help resolving this would be greatly

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mngong SSCertifiable Points: 6167 More actions · Answer 1

Drop and recreate the maintenance plan jobs

Mike

Reg_Mayfield SSCrazy Points: 2661 More actions · Answer 2

Mike,

I went ahead and ran the sp_dropserver, sp_addserver hoping that would solve my problem.

Again unable to delete the maintenance plan.

Next I tried dropping/deleting the job as you suggested and I get the following error:

Drop failed for Jox XXX (Microsoft.SqlServer.Smo)

Additional information:

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.Sqlserver.ConnectionInfo)

The DELETE statement conflicted with the REFERENCE constraint "FK-subplan_job_id" The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.

The statement has been terminated. (Microsoft SQL Server, Error:547)

mngong SSCertifiable Points: 6167 More actions · Answer 3

Can you disable the jobs or unschedule them without issues?

If so ,do that,create new ones that work then continue troubleshooting.

What is the result of

select @@servername

Mike

Reg_Mayfield SSCrazy Points: 2661 More actions · Answer 4

Hey Mike...

Here are the steps I used to resolve my problem.

First I deleted the Maintenance plans under SSIS

Then I ran the following scripts:

URL: http://gedzuks.wordpress.com/2007/04/20/sql-server-2005-delete-maintenance-plan-error/

Sql Server 2005 Delete Maintenance Plan Error

April 20th, 2007 · 3 Comments

Below is the manual SQL fix to address this error:

Drop failed for Job ‘XXXXXXXXXXXXX’. (Microsoft.SqlServer.Smo)
The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The statement has been terminated. (Microsoft SQL Server, Error: 547)

Here is the SQL to fix this:

–NOTE: Replace “MaintenancePlan” with the value in the Jobs under SQL Server Agent, put the job name

USE [msdb]

declare @job_name varchar(100)

set @job_name = N’MaintenancePlan’

–First, delete the logs for the plan

delete sysmaintplan_log

FROM sysmaintplan_subplans AS subplans INNER JOIN

sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id INNER JOIN

sysmaintplan_log ON subplans.subplan_id = sysmaintplan_log.subplan_id

WHERE (syjobs.name = @job_name)

–delete the subplan

delete sysmaintplan_subplans

FROM sysmaintplan_subplans AS subplans INNER JOIN

sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id

WHERE (syjobs.name = @job_name)

–delete the actual job (You can do the same thing through Management Studio (Enterprise Manager)

delete

from msdb.dbo.sysjobs_view where name = @job_name

If you get this error:

Msg 547, Level 16, State 0, Line 27
The DELETE statement conflicted with the REFERENCE constraint “FK__sysjobsch__job_i__276EDEB3?. The conflict occurred in database “msdb”, table “dbo.sysjobschedules”, column ‘job_id’.

Open the Job and Delete the Schedules

Once I ran the above scripts I was able to remove all jobs referencing unwanted Maintenance plans.

Mike I appreciate your help in running down this problem and hope the above information helps somebody else with the same issues.

e.gary Valued Member Points: 59 More actions · Answer 5

I failed on this problem twice since years, and for the second time this post helped me.

Thanks.