Post reply

Cannot Drop a SQL Agent Job

  • April 22, 2009 at 12:08 pm

    #155594

    I'm in the process of setting up a new instance of SQL Server 2005 SP3. All was well until I attempted to add one of my "normal" maintenance plans. Long story short, I used the wizard, cause I'm lazy and it screwed me. when I clicked finish to create the plan, it threw an error. I clicked ok and ran thorough the wizard again and all seemed to be well.

    It's been a few days now, and I was reviewing the execution of the SQL Agent jobs and noticed there was one called Maintenance Plan. I tried to delete it and was given the following error...

    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)

    I understand the error as a FK constraint error. It seems that when the plan was created that not all of it was rolled back when the error occurred during the creation of the plan. I know I just need to go into MSDB and work my way through the constrainsts to be able to delete this.

    My question is, has anyone else experienced anything like this with 2005 particularly with SP3? Should I be looking for other issues once I get this one is cleared up?

    TIA,

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • April 22, 2009 at 2:19 pm

    #982505

    If the job was created from maintenance plan, you should be able to drop the job by dropping the maintenance plan which created that job.

  • April 22, 2009 at 3:32 pm

    #982573

    Hope this helps:

    http://gudenas.com/2007/04/20/sql-server-2005-delete-maintenance-plan-error/

  • April 22, 2009 at 3:36 pm

    #982577

    Actually the maintenance plan itself was never actually created, but the job was. It was kinda strange. It just caused a few FK issues when I tried to clean it all up. It's fixed now, I was just curious if anyone else had seen any other issues with the wizards blowing up and trashing parts of the MSDB database. It's just something I hadn't come across with SSMS yet and thought it odd.

    Thanks,

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

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