April 2, 2018 at 1:14 pm
I have a maintenance plan that was removed and it won't delete from the SQL Server Agent Jobs. When I try to remove the jobs I get this error:
"Msg 547, Level 16, State 0, Procedure sp_delete_job, Line 182
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."
I did tones of Google searches and everytime I think I got the right answer (the query returned successful with rows effected), I would refresh the Jobs folder and see it was still there.
I ran the following queries/scripts:
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
------------------------------------------------------------------------------
'SELECT job_id from msdb.dbo.sysjobs where name = 'Mar2014_MonthBU.Subplan1' (this is the name of the job I want to delete).
'DELETE from msdb.dbo.sysmaintpaln_subplans where job_id = (insert job id from above query).
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'MyJob')
EXEC msdb.dbo.sp_delete_job @job_name=N'MyJob', @delete_unused_schedule=1
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT job_id, name, enabled, owner_sid, date_created, date_modified FROM msdb.dbo.sysjobs where name = 'MyJobNameHere'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Then finally, I ran this:
USE [MSDB]
go
--Delete the Log history for the maintenance plan affected
DELETE FROM sysmaintplan_log
WHERE subplan_id in
( SELECT Subplan_ID from sysmaintplan_subplans
-- change Subplan name where neccessary
WHERE subplan_name = 'Subplan_1' )
-- Delete the subplan
DELETE FROM sysmaintplan_subplans
WHERE subplan_name = 'Subplan_1'
------------------------------------------------------------------------------------------------------------
After all that, the script returned "successful" but the job is still there. I tried to remove the job again, and got the same error. I tried creating the maintenance plan again, but SQL wouldn't let me because of they use the same name. At this point I figured it was hopeless and created a new maintenance plan with a different name. The problem was it was the wrong one and needed to remove it. I could remove it from the Management folder, but not from the SQL Server Agent Jobs. So now I'm stuck with 2 jobs I cannot remove. I did everything but reboot. Usually whenever I need to remove a maintenance plan, I remove the plan first, then the job, refresh and its gone. I've never seen anything like this before. And why is it telling me successful/rows effected when the SQL Jobs Agent says otherwise?
Thanks.
April 3, 2018 at 5:18 am
This was removed by the editor as SPAM
April 3, 2018 at 5:48 am
Lj Burrows - Tuesday, April 3, 2018 5:18 AMThere could be various reasons of getting SQL Server error 547. The error occured when your child table does not match with the parent table.Go through the following link :
http://www.sql-server-helper.com/error-messages/msg-547-delete.aspx
Well this is odd...I didn't do anything else, went into SSMS just now, and was able to delete both maintenance plans. No errors. Could be a timing thing, but I'm all set now on this.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply