最近在清理一些不用的Job,发现几个跟MDW有关的。虽然Job已经被Disable, 但是没有被删除。尝试删除出现下面的错误： The DELETE statement conflicted with the REFERENCE constraintFK_syscollector_collection_sets_collection_sysjobs. The conflictoccurr
最近在清理一些不用的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和对象，其实看下这个存储过程的代码跟上面的差不多。
CREATE PROC [dbo].[sp_syscollector_cleanup_collector]
@collection_set_id INT = NULL
AS
BEGIN
IF (@collection_set_idIS NOT NULL)
BEGIN
DECLARE @retVal int
EXEC @retVal = dbo.sp_syscollector_verify_collection_set@collection_set_id OUTPUT
IF (@retVal 0)
BEGIN
RETURN (1)
END
END
DECLARE @TranCounter INT
SET @TranCounter = @@TRANCOUNT
IF (@TranCounter > 0)
SAVE TRANSACTIONtran_cleanup_collection_set
ELSE
BEGIN TRANSACTION
BEGIN TRY
-- changing isolation level to repeatable to avoid anyconflicts that may happen
-- while running this stored procedure andsp_syscollector_start_collection_set concurrently
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- Security check (role membership)
IF (NOT(ISNULL(IS_MEMBER(N'dc_admin'), 0) = 1) AND NOT(ISNULL(IS_MEMBER(N'db_owner'), 0) = 1))
BEGIN
REVERT
RAISERROR(14677, -1, -1, 'dc_admin')
RETURN (1)
END
-- Disable constraints
-- this is done to make sure that constraint logic does notinterfere with cleanup process
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 data collector jobs
DECLARE @job_id uniqueidentifier
DECLARE datacollector_jobs_cursor CURSOR LOCAL
FOR
SELECT collection_job_id ASjob_id FROM syscollector_collection_sets
WHERE collection_job_id ISNOT NULL
AND( collection_set_id =@collection_set_id OR @collection_set_id IS NULL)
UNION
SELECT upload_job_id ASjob_id FROM syscollector_collection_sets
WHERE upload_job_id ISNOT NULL
AND( collection_set_id =@collection_set_id OR @collection_set_id IS NULL)
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 WHEREjob_id = @job_id )
BEGIN
DECLARE @job_name sysname
SELECT @job_name = name from sysjobs WHERE job_id =@job_id
PRINT 'Removingjob '+ @job_name
EXEC dbo.sp_delete_job@job_id=@job_id,@delete_unused_schedule=0
END
FETCH NEXT FROM datacollector_jobs_cursor INTO @job_id
END
CLOSE datacollector_jobs_cursor
DEALLOCATE datacollector_jobs_cursor
-- Enable Constraints 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
-- Disable trigger on syscollector_collection_sets_internal
-- this is done to make sure that trigger logic does notinterfere with cleanup process
EXEC('DISABLE TRIGGERsyscollector_collection_set_is_running_update_trigger ONsyscollector_collection_sets_internal')
-- Set collection sets as not running state and updatecollect and upload jobs as null
UPDATE syscollector_collection_sets_internal
SET is_running = 0,
collection_job_id = NULL,
upload_job_id = NULL
WHERE (collection_set_id= @collection_set_id OR@collection_set_id IS NULL)
-- Enable back trigger onsyscollector_collection_sets_internal
EXEC('ENABLE TRIGGERsyscollector_collection_set_is_running_update_trigger ONsyscollector_collection_sets_internal')
-- re-set collector config store if there is no enabledcollector
DECLARE @counter INT
SELECT @counter= COUNT(is_running)
FROM syscollector_collection_sets_internal
WHERE is_running = 1
IF (@counter = 0)
BEGIN
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' )
END
-- Delete collection set logs
DELETE FROMsyscollector_execution_log_internal
WHERE (collection_set_id= @collection_set_id OR@collection_set_id IS NULL)
IF (@TranCounter = 0)
BEGIN
COMMIT TRANSACTION
END
RETURN(0)
END TRY
BEGIN CATCH
IF (@TranCounter = 0 OR XACT_STATE() = -1)
ROLLBACK TRANSACTION
ELSE IF (XACT_STATE() = 1)
ROLLBACK TRANSACTIONtran_cleanup_collection_set
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(),'-');
RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity,@ErrorState, @ErrorProcedure, @ErrorLine,@ErrorMessage);
RETURN (1)
END CATCH
END
