SO I have been having this really weird issue whereby when I create a maintenance plain in SQL Server 2005 Management studio, it fails to save it finally, hence wont be able to execute the plan. In this case I was trying to create a scheduled backup. And it keeps failing to save, and worse of all, it would not even delete when you try to remove it afterward. I had to ask THE GOOGLE for some help, and it turns out that I am not alone.
After a little bit of searching around,using this phrase from the error log "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)" I was able to find a way to delete the plan, thank to this link: http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/4a973abc-6675-4b5d-8c47-967ffc3679ea/ .
This specific post that solved it is:
Guys, On SQL2005, I had the same problem. Based on the previous Tips, I have cleaned up the Job Activity monitor.
Here is the steps followed.
1) Prefix the name of all the Jobs that you are having trouble deleting with 'DEL'
using Job Activity Monitor
2)
use msdb
go
-- Check whether the next Delete Statment deletes only the jobs you want to delete
select * from sysjobs where name like 'DEL%'
go
3)-- Run the following SQL to delete
delete from sysmaintplan_subplans
where job_id in(
select job_id from sysjobs where name like 'DEL%')
go
4) Go to Job Activity Monitor and Right Click to Delete Jobs
Cheers,
JamesPJ
So there it is!.. In case anyone stumbles across similar error. But I have a feeling I will need to reference this again sometime in the future.
By the way, the error was created because the sa login credential in sql is left with a blank password, hence using that authentication for the maintenance plan throws up the error. I didnt want to put a password on it to resolve the issue because I have an application that used that credential, so I ended up creating the plain with windows authentication.
