SQL Server SSMS Bug: Scripting Delete action to new query window for Maintenance plans is deleting the maintenance plans without even executing the script leaving jobs behind which throws errors during manual delete

SQL Server version: Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Tested using SSMS 17 and SSMS 12

Scripting Delete action to new query window for Maintenance plans is deleting the maintenance plans without actually executing the script:

Steps to reproduce:
  1. Create a maintenance plan using maintenance plan wizard
  2. This creates maintenance plan under path : Server or Instance Name>Management>Maintenance Plans along with a corresponding job under Sql server agent when viewed from SSMS
  3. Now right click on the Maintenance plan that gets created from step 1 in location specified in step 2 above and click delete
  4. But instead of clicking OK use the script button on top of window and select "script action to new query window" and then click CANCEL in the same window from where we just selected script action.
  5. So we just scripted the action and haven't actually deleted the plan nor by clicking OK nor by executing the script that gets created in new window.
  6. But now if you right click and refresh the maintenance plans folder in ssms you will notice that the maintenance plan has already got deleted.
  7. The other thing to note is that the corresponding job under SQL agent is not deleted when this happens so go ahead and refresh jobs folder under sqlagent in ssms and you will see the jobs still hanging around there and when you try to delete them manually you get the error :

Error/Exception: 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)

Solution for deleting such jobs:
  1. To resolve the above error that we are getting while deleting the job created for maintenance plans here is what is required to be done:
--Find the details for all the maintenance plans with problem using the below query 
use msdb;
select * from sysmaintplan_subplans
--once you have the subplan_name and job_id of the problematic left over job from maintenance plan which got deleted due to bug, execute the below query by replacing values for subplan_name and job_id with actual values :
DELETE FROM sysmaintplan_subplans
WHERE subplan_name = 'Subplan_1' and job_id='Replace this text with actual Job Id'
--and then execute the following statement to delete the job :
EXEC msdb.dbo.sp_delete_job @job_id=N'Replace this text with actual Job Id', @delete_unused_schedule=1
--This will delete the left over jobs.

Normally deleting maintenance plans delete the corresponding jobs automatically which means if you select OK instead of CANCEL in step 4 above you will not even notice this behavior.

Here is the Assembled screenshot for various steps taken above to reproduce the issue:

Reported to Microsoft : Report Link


Popular posts from this blog

Enabling fixed Sidebar for blogger Contempo template for screen sizes smaller than 1440px

Adding code prettify to Blogger

Adding copy to clipboard button to every google code prettify pre blocks in blogger