Post reply

Trouble deleting SQL 2005 Maintenance Plans/Jobs

  • August 7, 2007 at 3:49 am

    #197141

    I'm trying to delete several maintenance plans from SSMS and get the following error...

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

    An error was encountered when trying to remove the package "Maintenance Plans\User Databases - Full Daily Backup" from SQL Server.

    If I try and delete the associated SQL Agent Job I get a different error...

    Drop failed for Job 'User Databases - Full Daily Backup.Subplan_1'.  (Microsoft.SqlServer.Smo)

    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)

    I'm using SQL 2005 Standard Server Build 3054, what do I need to do in order to delete these plans/jobs from the msdb database?  The virtual server was built a few days ago and only has a dummy database created for the purpose of testing the user database maintenance plans.

    Both of the maintenace plans that I am trying to delete were initially created by exporting from one instance to another instance on the same server using SSIS.

    TIA.

  • August 7, 2007 at 4:01 am

    #725058

    Mark, I had the same issue last week what i did was queried the sysmaintplan_subplans and removed the entry for the maintenance plan from this system table.......then it was once agiain referencing another table i.e dbo.sysmaintplan_logs.......so you need to remove the entry for the maintenance plan from these 2 system tables which are available in msdb database

     

    [font="Verdana"]- Deepak[/font]

  • August 7, 2007 at 4:52 am

    #725073

    Thanks Deepak, i've removed them from sysmaintplan_subplans but there are no references to these plans under sysmaintplan_log.  However, they are still appearing under Maintenance Plans in SSMS.

    Do you know which table/s I need to remove them from so they no longer appear in SSMS?

    Cheers.

  • August 7, 2007 at 4:52 am

    #725072

    Thanks Deepak, i've removed them from sysmaintplan_subplans but there are no references to these plans under sysmaintplan_log.  However, they are still appearing under Maintenance Plans in SSMS.

    Do you know which table/s I need to remove them from so they no longer appear in SSMS?

    Cheers.

  • August 7, 2007 at 4:58 am

    #725076

    Try deleting the jobs and it will generate an error if any this time can you try it and get back to me

    [font="Verdana"]- Deepak[/font]

  • August 7, 2007 at 5:30 am

    #725086

    All the SQL Agent jobs have been removed, I just have the 2 maintenance plans listed under Management, Maintenance Plans.  If I try and delete these then I get the following error.

    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\User Databases - Full Daily Backup" from SQL Server.

    An error was encountered when trying to remove the package "Maintenance Plans\User Databases - Full Daily Backup" from SQL Server.

  • August 7, 2007 at 5:38 am

    #725088

    I can see both maintenance plans listed under dbo.sysdtspackages90 not sure if I should try and delete them from this table?

  • August 7, 2007 at 6:18 am

    #725100

    Ok, having removed them from the dbo.sysdtspackages90 table they no longer appear under Maintenance Plans.

    thanks deepak for you help.

  • August 7, 2007 at 7:48 am

    #725144

    Mark, Thanks for providing the solution for your problem....so that those who view this thread will be benifited...........

    [font="Verdana"]- Deepak[/font]

  • March 4, 2009 at 8:49 am

    #953288

    I actually had the same issue after renaming a SQL Server running SQL Server 2008. Here's what I had to do:

    First, I renamed the SQL Server:

    1. Should see old name:

    SELECT @@SERVERNAME;

    GO

    2. Remove that guy:

    EXECUTE dbo.sp_dropserver 'SQL Server Listed Above';

    GO

    3. Add current server name:

    EXECUTE dbo.sp_addserver 'New SQL Server', local

    GO

    4. Restart MSSQLSERVER Service.

    5. Verify:

    SELECT @@SERVERNAME;

    GO

    Next, I removed the job:

    1. Get subplan_id from sysmaintplan_subplans and delete that data from sysmaintplan_log.

    2. Get job_id from sysjobs and delete from sysmaintplan_subplans.

    3. Delete the job.

    Finally, I removed Maintenance Plan:

    1. Delete the row from the dbo.sysssispackages table containing the maintenance plan that will not delete above.

    No other jobs were having problems for me. Only the maintenance plans generated on the old server.

  • March 17, 2011 at 3:16 am

    #1299831

    the maintenance plans details are populated among the following tables. u should delete the relevant records from all the 4 tables in order to make it vanish in SSMS. since there are relationships established, start deleting the records in the given order

    sysmaintplan_logdetail

    sysmaintplan_log

    sysmaintplan_subplans

    sysmaintplan_plans

    after the records are deleted, refresh maintenance plan folder in SSMS. all the old maint plans will be vanished !!

    then u can delete the relevant jobs without any error 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply