Stop the Hollyweb! No DRM in HTML5.   

Wednesday, July 1, 2009

Can’t Delete Jobs (Microsoft SQL Server, Error: 547)

I was creating a new job to handle weekly database maintenance and when I attempted to delete the old job, I received the following error;

TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Job 'Weekly_Maintenance'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476

------------------------------
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)
------------------------------


If you try deleting the job from the sysmaintplan_subplans table, you also get the following error:

Msg 547, Level 16, State 0, Line 41

The DELETE statement conflicted with the REFERENCE constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_log", column 'subplan_id'.


I wanted to create a stored procedure that I could just pass a job name to and it would check each of the tables and delete all references for me. Here is what I came up with.

CREATE PROC dbo.DropJob
@JobName AS VARCHAR(200) = NULL
AS

DECLARE @msg AS VARCHAR(500);

IF @JobName IS NULL
BEGIN
SET @msg = N'A job name must be supplied for parameter @JobName.';
RAISERROR(@msg,16,1);
RETURN;
END
IF EXISTS (
SELECT subplan_id FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN

( SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName)))
BEGIN
DELETE FROM msdb.dbo.sysmaintplan_log WHERE subplan_id IN

( SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName));

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName);

EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
ELSE IF EXISTS (
SELECT subplan_id FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName))
BEGIN

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE job_id IN
(SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName);

EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;
END
ELSE
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name=@JobName, @delete_unused_schedule=1;

END
GO

Now you can call the SP with the following;

USE [msdb];
EXEC dbo.DropJob @JobName = N'Shrink_AWP_Databases.Subplan_1';

13 comments:

  1. Thank you very much. Very useful.
    Aprreciate yoru time and efforts
    Rick

    ReplyDelete
  2. Thanks, but it didnt work for me. The job is not showing in my sysmaintplan_plans and I get the error TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Drop failed for Job 'Rld'. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476

    ------------------------------
    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)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    ReplyDelete
  3. were you able to locate the job in msdb.dbo.sysmaintplan_subplans?

    ReplyDelete
  4. Hi ,

    i have got a problem in my environment, The SQL server Agent errror logs is continously filled up with below error messages
    "[298] SQLServer Error: 14262, The specified @job_id ('74AF9B09-0C54-449E-8BDC-3D323D570D1F') does not exist. [SQLSTATE 42000] (ConnExecuteCachableOp"

    After several research, i got to know that
    EXEC sp_delete_job @job_id='74AF9B09-0C54-449E-8BDC-3D323D570D1F' statement is running contionously at background , which is making these error messagges entries.
    I even restarted the SQL Agent service , but still not helped.

    Please guide me in resolving this problem.
    thanks in advance.

    ReplyDelete
  5. Perfect. Took a while but it completed and the job was gone. Nice one!

    ReplyDelete
  6. I have a stubborn job I couldn't delete. This stored proc did the trick. Thank You!

    ReplyDelete
  7. I deleted jobs without problems. Thanks!

    ReplyDelete
  8. Worked perfectly, solved my problem! Thank you for sharing this solution.

    ReplyDelete
  9. Good day. I had the same, Jobs do not want to delete. I've tried the sp, but still no success. Then I use DBCC CHECKDB (msdb), which point to the following table with errors: dbo.sysjobactivity, then I went to that table, rebuild the index and success, all jobs can be delete that refuse before.

    ReplyDelete
  10. Awesome! Exactly what I was looking for! Thank you, Clay! :-)

    ReplyDelete