August 27, 2016 at 11:00 am
Cleaning up a bit, I found an old job that has been failing for a while. It's a maintenance job, supposed to be doing weekly backups.
This is a personal instance, mostly used for testing locally, so no critical data has been endangered. 😀
Trying to delete it fails with this error:
"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'."
Before I get really crazy here, is there a way to delete the job via an alternative method?
I could just disable it and create a new one, but, well, now that I know it's a problem, I HAVE to fix it. Preferably deleting it, but I guess if I can't, then I need to determine why it's now failing and address that. I suspect it's got something to do with the account under which it runs.
Thanks in advance.
August 27, 2016 at 11:20 am
I figured out that the SID of the alleged owner of the job doesn't exist. Apparently that's why I can't delete it.
Can I change the owner to dbo and then delete it?
August 27, 2016 at 1:03 pm
Did you try deleting the maintenance plan or try removing the schedule from the maintenance plan?
I think you are probably hitting an issue with the maintenance plan, a bit of a known issue.
If you still get the errors trying to remove the job from the maintenance plan or trying to delete the maintenance plan, you need to delete the log (from sysmaintplan_log) and subplan (from sysmaintplan_subplans) for the maintenance plan.
Sue
August 27, 2016 at 2:13 pm
Thanks. I'll give that a whirl.
August 27, 2016 at 3:01 pm
And, yes, I ended up deleting the subplan from the log and the subplan tables first. Thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply