Unable to Delete Maintenance Plan in SQL: What to Do?

SQL Server has in-built functionality for easy maintenance of database which is commonly known as database maintenance plans. It comes with intuitive interface and allow you to automate simple maintenance tasks. Maintenance plan in SQL is created and maintain on regular basis to keep track of your database activities. It allows you to perform various database administration tasks like importing transaction logs to another server, updation of database statistics, run integrity checks to ensure database consistency, perform database dumps and many more. While using it at times it happens that the plan doesn't do anything and you are unable to perform any tasks. There might arises a situation when you need to modify or delete it but unable to delete maintenance plan in SQL due to any adverse causes.

Most of the SQL users have reported that due to improper functioning of plan they need to modify or delete it but due to occurrence of error message they are unable to do so. Often it happens that when users attempt to delete maintenance plan in SQL using SQL Server Management Studio i.e. SSMS it give rise to following error message:

"An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) -> The SELECT permission was denied on the object 'sysmaintplan_plans', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)"

As a result of above error message users are unable to delete maintenance plan in SQL neither able to modify it. the problem might arises when you have updated the SQL version to Service Pack 2 and the maintenance which you want to delete has been created in previous versions. Sometimes it might happens that you are able to delete the maintenance plans but unable to delete the jobs and come across another error message.

Steps to Delete Maintenance Plan in SQL

  • At first extract the maintenance plan name and Id by executing below query
    SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS
  • You will obtain the Id which you have to replace into below query and then delete the entry from log table
    DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' '
  • In this step delete the entry from subplans table as shown:
    DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '
  • Now you have to delete the maintenance plan by providing the Id given in step 1 and executing below query
    DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '
  • At last you have to check and delete jobs entirely from SSMS

In this way you can easily delete the maintenance plan. Sometimes it might happens that plan gets corrupted due to which you are unable to delete maintenance plan in SQL. So you need to repair it and then try to modify or delete it.