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;
go
select * from sysmaintplan_subplans
go
--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
GO
--This will delete the left over jobs.

Note:
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

Comments

  1. Some proponents of the video slot machines say playing 카지노사이트 addictions have always been current however had been previously limited to casinos. There’s not necessarily more playing addicts, they are saying; it’s just that the addictions weren’t as seen before. The Missouri Gaming Commission, nonetheless, nonetheless sees them as “gambling units,” which are thought of unlawful outside of licensed casinos. The grey boxes are unregulated and not taxed, and it’s unclear whose job it is to seek out and take away them.

    ReplyDelete
  2. Name a loss limit someplace between 1-5% of your whole bankroll. This will give you you with|provides you with} loads of alternatives to win in small increments and avenge your losses without putting yourself in pointless danger. In you’re a gifted enough participant, you have the ability to|could possibly|might find a way to} find another person to finance you in change for a portion of your winnings. 토토사이트 Unique applications, similar to art therapy, create a dynamic educational group.

    ReplyDelete

Post a Comment

Note:Please be gentle while commenting and avoid spamming as comment are anyways moderated thank you.

Popular posts from this blog

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

Script to delete the files older then 2 days from All Available Drives On Servers:\AnyDirectory

Adding Home Older Newer Post Buttons in new Blogger templates such as Contempo