Skip to content

Not able to delete SQL Server Maintenance Plan and/or SQL Server Agent jobs

You may get below error while trying to delete MP after server name change: –

TITLE: Microsoft SQL Server Management Studio

Exception has been thrown by the target of an invocation. (mscorlib)

ADDITIONAL INFORMATION:

An error was encountered when trying to remove the package “Maintenance Plans\UserDB_Tlog” from SQL Server. 

and/or

You may get below error while trying to delete SQL Server agent job: –

TITLE: Microsoft SQL Server Management Studio

Drop failed for Job ‘UserDB_Tlog.Subplan_3’.  (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)

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

Possible cause of this issue: –

Mainly you will face issue if your SQL Server host name is changed.

Solution: –

In order to delete the maintenance plan and/or SQL Server agents jobs, we have to manually delete the entries from system tables in msdb database. Below are the queries you need to fire in msdb database in order to get this done.

select * from sysjobs -- to find the correct jobid

select * from sysjobschedules where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from [dbo].[sysjobschedules] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

select plan_id from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

select * from dbo.sysmaintplan_log where plan_id = '755708E5-5B24-4B31-8AC3-38944E6A99A3' -- planid is the value from above sql

delete from [dbo].[sysmaintplan_log] where plan_id = '755708E5-5B24-4B31-8AC3-38944E6A99A3' -- planid is the value frm above sql

select * from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from [dbo].[sysmaintplan_subplans] where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

delete from sysjobs where job_id ='B0C44798-7ABC-456B-B819-C369CE80150C' -- update job_id with your job id number

Now connect to integration services on this server and manually delete the package under msdb

Comments

  1. Komal says:

    I have these errors inP6.2 When I tried start db cofnig:Bad public user name or password. Database Server Error: Named Pipes Provider: Could not open a connection to SQL Server [2]. ;And the same time inSQL Server Configuration Manager: the request failed or service not respond in a timely fashion event log.-2010-07-30 11:19:20.28 Server Microsoft SQL Server 2005 9.00.3080.00 (Intel X86)Sep 6 2009 01:43:32Copyright (c) 1988-2005 Microsoft CorporationExpress Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 3)2010-07-30 11:19:20.28 Server (c) 2005 Microsoft Corporation.2010-07-30 11:19:20.28 Server All rights reserved.2010-07-30 11:19:20.28 Server Server process ID is 3324.2010-07-30 11:19:20.28 Server Authentication mode is MIXED.2010-07-30 11:19:20.28 Server Logging SQL Server messages in file C:\Program Files\MSSQL\Primavera\MSSQL.1\MSSQL\LOG\ERRORLOG’.2010-07-30 11:19:20.30 Server This instance of SQL Server last reported using a process ID of 1044 at 30/07/2010 11:14:01 a.m. (local) 30/07/2010 04:14:01 p.m. (UTC). This is an informational message only; no user action is required.2010-07-30 11:19:20.30 Server Registry startup parameters:2010-07-30 11:19:20.30 Server -d C:\Program Files\MSSQL\Primavera\MSSQL.1\MSSQL\DATA\master.mdf2010-07-30 11:19:20.30 Server -e C:\Program Files\MSSQL\Primavera\MSSQL.1\MSSQL\LOG\ERRORLOG2010-07-30 11:19:20.30 Server -l C:\Program Files\MSSQL\Primavera\MSSQL.1\MSSQL\DATA\mastlog.ldf2010-07-30 11:19:20.30 Server Error: 17113, Severity: 16, State: 1.2010-07-30 11:19:20.30 Server Error 2(error not found) occurred while opening file C:\Program Files\MSSQL\Primavera\MSSQL.1\MSSQL\DATA\master.mdf’ to obtain cofniguration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

  2. Rajkumar says:

    I cant able to delete the job which even find in the maintenace paln alsol in sysjobs.
    Actually I scheduled the full backup on night 9.30 but it occurs daily at 9.30 as well as 10 pm. How to rectify it.

    1. admin says:

      Are you sure there is no other backup job which runs for this database?

  3. Kleidi says:

    This method works great. Just to make things a bit simpler add this to the beginning of the script and no need to copy/paste your job ID averytime.

    DECLARE @jID AS UNIQUEIDENTIFIER
    DECLARE @pID AS UNIQUEIDENTIFIER

    select * from sysjobs — to find the correct jobid
    SET @jID = ‘copy paste here the job id and then excecute the whole script provided by just adding @jID and @pID when each is needed.’
    SET @pID = (select plan_id from [dbo].[sysmaintplan_subplans] where job_id = @jID )

  4. Rockie says:

    Thank You very much!

    It helped!

    Best regards, Gennady

  5. Stacy says:

    Thanks, this worked great. Bookmarked. Also, thanks Kleidi for the declare suggestion