May 27, 2010 at 9:20 am
i get the error that system table are having referential constraint.
with the job i am trying to delete.
May 27, 2010 at 9:28 am
You really need to post more information about what you are doing, how, the code, and the exact error message.
Please don't assume the title of your post is part of the question. Repeat what the problem is in the post.
May 27, 2010 at 9:54 am
On SQL Management studio under Agent/Jobs/
there are schedule tasks that get executed by the agent.
Some of these jobs have been disabled for Ages. my task is to identify and clean up these unused Jobs.
error " Drop failed for job 'full_database-backup' An Exception occured while executing Transact-SQL statement or batch" Delete statement conflicted with the REFERENCE Constraint "FK_SUBPLAN_JOB_ID". ............
Please checkout the attachment for further understanding.
Hope this can help. please assist me.
May 27, 2010 at 10:08 am
The job you are deleting is linked to a maintenance plan. Clear the maintenance plan first.
May 28, 2010 at 3:37 am
i didi that first and still i get the the same error.
May 28, 2010 at 8:57 am
Perhaps the wrong one is gone?
The error you give relates to a row in dbo.sysmaintplans_subplans. You should be able to query the tables in MSDB to get more information about which job is related to which plan.
May 28, 2010 at 9:06 am
i have tried that and honestly the msdb tables have more than one referential integrity.
For me to succeed i need a way of write a delete script that deletes from system tables which i have fear off since this is a live environment.
May 28, 2010 at 9:23 am
You might have deleted the plan, but there wasn't RI in the system tables to clear out the subplan. That wouldn't surprise me. I can't make it happen, but it's possible that's what occurred.
I'd do one of two things
1. change the jobid in the subplan table so you can delete the job.
2. delete the subplan if you are positive that it's not being used.
Do you have other maintenance plans on the server? Are you sure this isn't a part of the maintenance plans? Have to traced back all the IDs in the jobs to the steps to the subplans/plans and ensure they're not being used?
May 28, 2010 at 9:30 am
oK, I might not have checked most of the suggestions you have aske now, but i am quite sure that this plans id is not in use by other plans, the reasons i want to delete it its because there is a new healthy plan configured similar to the one i want to delete and it running fine.
Remember i want to delete it because it does not work anymore thats why it was disable in the first place. I think the preveus DBA might have struggled with this issue because i found it like that. I just want to make my new environment look neat.
May 28, 2010 at 9:32 am
Completely understand. Try tracing things down. I have had similar issues in the past with master/target servers in SQL 2000 and had to update system tables to clean things up.
What I might also suggest is that you rename the job to "zzz DO NOT ENABLE" to move it to the bottom of your list.
Can you remove the schedule for this job?
May 28, 2010 at 9:37 am
Yes you can remove the schedule successfully!! At first i even thought removing of the schedule before the Job could have been the cause but i have tested this on my virtual environment its not the case.
Thanks i shall rename the machine and just give my self some more time with System DBs.
May 28, 2010 at 9:43 am
Good luck. If you figure out what happened, or use code to clean things up, post back. I'm sure someone else will need the help later
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply