Friday, April 2, 2010

How to Delete Maintenance Plan In SQL Server

We need to perform the below steps to delete the jobs or maintenance plans, in such cases from sysmaintplan_log table or from sysmaintplan_subplans table. 

Step 1:
Execute the below query to obtain the Maintenance plan name and Id
SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS


Step 2:
Replace the Id obtained from Step 1 into the below query and delete the entry from log table
DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' ' 


Step 3:
Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below,
DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' ' 


Step 4:
Finally delete the maintenance plan using the below query where ID is obtained from Step1
DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' ' 


Step 5:
Check and delete the jobs from SSMS if it exists.

how to delete database maintenance plan, how to configure maintenance plan in sql server 2008 r2, how to delete maintenance plan in sql server 2008 r2, how to delete maintenance plan in sql server 2012. how to drop maintenance plan, how to create maintenance plan,

0 comments:

Post a Comment