Topic: Resolution for the Maintenance plan error “The INSERT statement conflicted with the FOREIGN KEY constraint “FK_sysmaintplan_log_subplan_id”.” At SQL server 2008 R2.
We have faced the strange issue at sql server 2008 R2 and suddenly start to failed all the backup maintenance plan due to above error.
Reason: This is caused by multi connections stored within maintenance plan. When we create or edit a maintenance plan from another instance, it creates another connection. It happened cause sometime we are register the sql server from other box and want to modify the current maintenance plan as it automatically saves a new connection and tries to insert job history in new connection causing FK Violation and eventually job fails.
Resolution: Please upgrade the service pack 2 in sql server 2008 R2 environment to resolve the issue permanently or recreate the new maintenance plan as like old. To avoid such incident Please login to same sql server instance whenever we wish to update\modify any main plan and don’t use other box to connect the server.