delete sql maintenance plan - Techist - Tech Forum

Go Back   Techist - Tech Forum > Internet > Computer Networking and Internet Hardware > Networking Tips, Tricks & FAQ
Click Here to Login
Closed Thread
 
Thread Tools Display Modes
 
Old 07-02-2009, 01:38 PM   #1 (permalink)
It's all just 1s and 0s
 
office politics's Avatar
 
Join Date: Jan 2004
Location: in the lab
Posts: 6,555
Send a message via MSN to office politics
Default delete sql maintenance plan

2005 by default was throwing errors relating to foriegn key constraints. i ran the following code and was able to delete the maintenance plan.

Sql Server 2005 Delete Maintenance Plan Error Gediminas (Gedas) Gudenas

Quote:
Sql Server 2005 Delete Maintenance Plan Error
April 20, 2007 · 26 Comments
Below is the manual SQL fix to address this error:

Drop failed for Job ‘XXXXXXXXXXXXX’. (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 statement has been terminated. (Microsoft SQL Server, Error: 547)

Here is the SQL to fix this:

–NOTE: Replace “MaintenancePlan” with the value in the Jobs under SQL Server Agent, put the job name

USE [msdb]
declare @job_name varchar(100)
set @job_name = N'MaintenancePlan'


–First, delete the logs for the plan

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)


–delete the subplan

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)


–delete the actual job (You can do the same thing through Management Studio (Enterprise Manager)

delete
from msdb.dbo.sysjobs_view where name = @job_name


If you get this error:

Msg 547, Level 16, State 0, Line 27
The DELETE statement conflicted with the REFERENCE constraint “FK__sysjobsch__job_i__276EDEB3″. The conflict occurred in database “msdb”, table “dbo.sysjobschedules”, column ‘job_id’.

Open the Job and Delete the Schedules
office politics is offline  
Closed Thread

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to obtain SQL Server FullText Search service? BobLewiston Programming 9 08-14-2009 08:24 AM
dataAdapter.Update / SQL PasswordHash NULL problem BobLewiston Programming 0 03-30-2009 12:04 AM
Wired, Non Router, Game Connecting Problem reggordon Computer Networking and Internet Hardware 2 01-10-2009 03:31 AM
Explorer.exe CRASHES INSTANTLY =( [F] wootwoot HijackThis Logs (finished) 28 07-15-2008 02:47 PM
Building a Gaming Rig dark strike1 New Systems | Building and Buying 47 07-29-2007 03:57 PM



Copyright 2002- Social Knowledge, LLC All Rights Reserved.

All times are GMT -5. The time now is 01:13 AM.


Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.