Are you unable to delete maintenance plan in SQL? Have no idea what to do next? Well, then fortunately you are right post. Continue Reading and get complete solution to overcome the problem.
As we all know, MS SQL is widely used relational database management system sue to its reliability and effectiveness. SQL Server has in-built functionality that offer 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. However, in spite of advanced features sometime 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 where you need to modify or delete it but you will be unable to delete maintenance plan in SQL due to any adverse causes.
Number of SQL users have reported that due to improper functioning of plan and due to occurrence of error message they are unable to modify or delete it. 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
Going this way you can easily delete the maintenance plan. But sometimes it happens that plan gets severely corrupted due to which you are unable to delete maintenance plan in SQL. So in that case you need to repair it and then try to modify or delete it. You can do it manually but for that you need to have technical knowledge that’s why you are advised to rely on effective SQL Repair Software such as MS SQL Database Repair Software which has been designed excellently with simple interface so that even novice can use it in easy manner. It has advanced technology that easily repair database and recover deleted or lose record easily. Therefore you must try MS SQL Database Repair Software if you are unable to delete maintenance plan in SQL.