最近在清理一些不用的Job,发现几个跟MDW有关的。虽然Job已经被Disable, 但是没有被删除。尝试删除出现下面的错误：
The DELETE statement conflicted with the REFERENCE constraint"FK_syscollector_collection_sets_collection_sysjobs". The conflictoccurred in database "msdb", table"dbo.syscollector_collection_sets_internal", column 'collection_job_id'.
The statement has been terminated. (.Net SqlClient Data Provider)
查了一些文档发现这个问题在2008/2008 R2中都存在，只能Disable但是无法删除。找到了一篇文章Removeassociated data collector jobs提供了代码去删除MDW的相关job和对象.虽然文章提到只能在测试环境中运行这个脚本，但是这个脚本不会对其他应用产生影响。
USE MSDB
GO
-- Disableconstraints
ALTER TABLE dbo.syscollector_collection_sets_internal NOCHECK CONSTRAINTFK_syscollector_collection_sets_collection_sysjobs
ALTER TABLE dbo.syscollector_collection_sets_internal NOCHECK CONSTRAINTFK_syscollector_collection_sets_upload_sysjobs
-- Delete datacollector jobs
DECLARE @job_id uniqueidentifier
DECLARE datacollector_jobs_cursor CURSORLOCAL
FOR
SELECTcollection_job_id AS job_id FROM syscollector_collection_sets
WHEREcollection_job_id IS NOTNULL
UNION
SELECTupload_job_id AS job_id FROM syscollector_collection_sets
WHEREupload_job_id IS NOTNULL
OPEN datacollector_jobs_cursor
FETCH NEXT FROM datacollector_jobs_cursor INTO @job_id
WHILE (@@fetch_status = 0)
BEGIN
IF EXISTS( SELECT COUNT(job_id) FROM sysjobs WHERE job_id =@job_id )
BEGIN
DECLARE@job_name sysname
SELECT@job_name = name fromsysjobs WHERE job_id =@job_id
PRINT 'Removing job '+@job_name
EXECdbo.sp_delete_job@job_id=@job_id, @delete_unused_schedule=0
END
FETCH NEXT FROMdatacollector_jobs_cursor INTO @job_id
END
CLOSE datacollector_jobs_cursor
DEALLOCATE datacollector_jobs_cursor
-- EnableConstraints back
ALTER TABLE dbo.syscollector_collection_sets_internal CHECK CONSTRAINTFK_syscollector_collection_sets_collection_sysjobs
ALTER TABLE dbo.syscollector_collection_sets_internal CHECK CONSTRAINTFK_syscollector_collection_sets_upload_sysjobs
-- Disabletrigger on syscollector_collection_sets_internal
EXEC('DISABLE TRIGGERsyscollector_collection_set_is_running_update_trigger ONsyscollector_collection_sets_internal')
-- Setcollection sets as not running state
UPDATE syscollector_collection_sets_internal
SET is_running = 0
-- Updatecollect and upload jobs as null
UPDATE syscollector_collection_sets_internal
SET collection_job_id = NULL, upload_job_id =NULL
-- Enable backtrigger on syscollector_collection_sets_internal
EXEC('ENABLE TRIGGERsyscollector_collection_set_is_running_update_trigger ONsyscollector_collection_sets_internal')
-- re-setcollector config store
UPDATE syscollector_config_store_internal
SET parameter_value = 0
WHERE parameter_name IN('CollectorEnabled')
UPDATE syscollector_config_store_internal
SET parameter_value = NULL
WHERE parameter_name IN( 'MDWDatabase', 'MDWInstance' )
-- Deletecollection set logs
DELETE FROMsyscollector_execution_log_internal
GO
运行之后会看到下面的结果：
Removing jobcollection_set_3_collection
Removing jobcollection_set_2_upload
Removing jobcollection_set_1_noncached_collect_and_upload
Removing jobcollection_set_2_collection
Removing jobcollection_set_3_upload
可以看到跟ＭＤＷ有关的job已经被移除。另外提一下在2012中微软提供了一个新的系统存储过程sp_syscollector_delete_collection_set，可以删除自定义的MDW job和对象，其实看下这个存储过程的代码跟上面的差不多