Drop failed for jobs
-
I have a problem with some jobs, disabled since not useful, which I cannot drop. The message returned is
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Job 'copiaPS.Subplan'. (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 DELETE statement conflicted with the REFERENCE constraint "FK_subplan_schedule_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'schedule_id'.
The statement has been terminated.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
Thinking that some references were lost in msdb, I tried to restore a previous copy of msdb in another database, and look for related records, but I didn't find anything. Now I don't dispose of a copy of msdb containing the 'good' data. How can I drop the jobs?
Thanks,
sb
-
Hi,
In 2000 we can run dml operation on the tables. Get the job id and delete the corresponding records in the child tables. It works fine. In 2005 we have try it out.
-
Please, could you tell me how to find the job id and from which tables delete its records? The tables I looked in had details different from the ones I expected, maybe I loooked in the wrong ones.
-
It looks like the job may be associated with a maintenance plan. Try to find that and delete the maintenance plan first. That may also delete the job.
-
Charles Hottle (1/29/2009)
It looks like the job may be associated with a maintenance plan. Try to find that and delete the maintenance plan first. That may also delete the job.It seems so, as the job name in the OP is ''copiaPS.Subplan'. It should be a part of a maintenance pan.
Please Go to SSMS-->>Management--->>Maintenance Plans--->>find the CopiaPS and delete it from there.
Make sure you open the plan and check what's really in there then determine whether you really want to delete it.
-
I had also face this type of problem....
If your password is blank then also SSMS will give this type of error.
So set the password and try to delete the job...
it will work..
-
I also encountered this problem, and resolved as following:
USE [msdb]
declare @job_name varchar(100)
set @job_name = N'SystemDB-MaintenancePlan.Subplan_1'
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)
USE [msdb]
declare @job_name varchar(100)
set @job_name = N'SystemDB-MaintenancePlan.Subplan_1'
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)
declare @job_name varchar(100)
set @job_name = N'SystemDB-MaintenancePlan.Subplan_1'
delete
from msdb.dbo.sysjobs_view where name = @job_name
delete
FROM msdb.dbo.sysmaintplan_plans
where name = 'SystemDB-MaintenancePlan'
Hoping can help u:)
-
Maybe this link will help you , give it a try and let me know if it helped.
http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/4a973abc-6675-4b5d-8c47-967ffc3679ea/
-
Thanks for the help. This resolved my problem
-
Could you please share what did exactly helped you to resolve the problem.
-
Delete the schedule for the agent jobs realated to the maintenance plans. And you will be able to delete the maintenance jobs with out errors.
The fk error is because, agent job is pointing to the jobs schedule table.
-
For deleting a job whose maint plan does not exist and returns the error mentioned in the above discussion
Run the below query and identify the job_id of the job which needs to be deleted.
Use MSDB
GO
select * from dbo.sysjobs
Then use the below query
delete from dbo.sysmaintplan_subplans where job_id = 'job_id'
After this delete the job from SQL server agent jobs
-
prefix the job(s) you want to delete with xxx (i.e., xxxFullDatabaseBackups)
--NOTE: uncomment the delete part and comment out the select after you make sure the select --statement returns the correct records you want to delete.
--*SCRIPT*--
--delete sysmaintplan_subplans
select *
FROM sysmaintplan_subplans AS subplans INNER JOIN
sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id
WHERE (syjobs.name like '%xxx%')
--GO INTO SQL AGENT AND RIGHT CLICK TO DELETE THOSE JOBS MANUALLY...
-
You delete manually from the tables using the below steps for sql 2008 or above
1st. Find the job id from sysjobs table in msdb:SELECT [job_id]
,[name]
FROM [msdb].[dbo].[sysjobs] where name = 'xxxx'2nd . Delete the sysjobsshcedules: Delete from dbo.sysjobschedules
Where job_id = 'put the job id here'3rd delete from dbo.sysmaintplan_subplans table
Delete from dbo.sysmaintplan_subplans
Where job_id = 'put the job id here'4th Find the Subplan_id for the job_id:
Select * from dbo.sysmaintplan_subplans --- get the subplan_id from this query
Where job_id = 'put the job id here'5th delete from dbo.sysmaintplan_log table
Delete from dbo.sysmaintplan_log
Where subplan_id = 'put the subplan_id here'6th Finally delete from sysjobs table:
Delete from [msdb].[dbo].[sysjobs]
where job_id = 'put the job id here'refresh and check the Agent Job Activity monitor the job should not be in list.
hope this was helpful.
cheers
- Its just like referential integrity between two tables. So deleting the plan is the first step.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply