最近在清理一些不用的Job,发现几个跟MDW有关的。虽然Job已经被Disable, 但是没有被删除。尝试删除出现下面的错误：

 

The DELETE statement conflicted with the REFERENCE constraint "FK_syscollector_collection_sets_collection_sysjobs". The conflict occurred 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但是无法删除。找到了一篇文章Remove associated data collector jobs提供了代码去删除MDW的相关job和对象.虽然文章提到只能在测试环境中运行这个脚本，但是这个脚本不会对其他应用产生影响。

USE MSDB

GO

-- Disable constraints

ALTERTABLE dbo.syscollector_collection_sets_internal NOCHECKCONSTRAINT FK_syscollector_collection_sets_collection_sysjobs

ALTERTABLE dbo.syscollector_collection_sets_internal NOCHECKCONSTRAINT FK_syscollector_collection_sets_upload_sysjobs

 

-- Delete data collector jobs

DECLARE @job_id uniqueidentifier

DECLARE datacollector_jobs_cursor CURSORLOCAL

FOR

SELECT collection_job_id AS job_id FROMsyscollector_collection_sets

WHERE collection_job_id ISNOTNULL

UNION

SELECT upload_job_id AS job_id FROMsyscollector_collection_sets

WHERE upload_job_id ISNOTNULL

 

OPEN datacollector_jobs_cursor

FETCHNEXTFROM datacollector_jobs_cursor INTO @job_id

WHILE (@@fetch_status= 0)

BEGIN

IFEXISTS(SELECTCOUNT(job_id)FROM sysjobs WHERE job_id = @job_id )

BEGIN

DECLARE @job_name sysname

SELECT @job_name = name from sysjobs WHERE job_id = @job_id

PRINT'Removing job '+ @job_name

EXEC dbo.sp_delete_job@job_id=@job_id, @delete_unused_schedule=0

END

FETCHNEXTFROM datacollector_jobs_cursor INTO @job_id

END

CLOSE datacollector_jobs_cursor

DEALLOCATE datacollector_jobs_cursor

 

-- Enable Constraints back

ALTERTABLE dbo.syscollector_collection_sets_internal CHECKCONSTRAINT FK_syscollector_collection_sets_collection_sysjobs

ALTERTABLE dbo.syscollector_collection_sets_internal CHECKCONSTRAINT FK_syscollector_collection_sets_upload_sysjobs

 

-- Disable trigger on syscollector_collection_sets_internal

EXEC('DISABLE TRIGGER syscollector_collection_set_is_running_update_trigger ON syscollector_collection_sets_internal')

 

-- Set collection sets as not running state

UPDATE syscollector_collection_sets_internal

SET is_running = 0

 

-- Update collect and upload jobs as null

UPDATE syscollector_collection_sets_internal

SET collection_job_id =NULL, upload_job_id =NULL

 

-- Enable back trigger on syscollector_collection_sets_internal

EXEC('ENABLE TRIGGER syscollector_collection_set_is_running_update_trigger ON syscollector_collection_sets_internal')

 

-- re-set collector 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')

 

-- Delete collection set logs

DELETEFROM syscollector_execution_log_internal

 

GO

运行之后会看到下面的结果：

Removing job collection_set_3_collection

Removing job collection_set_2_upload

Removing job collection_set_1_noncached_collect_and_upload

Removing job collection_set_2_collection

Removing job collection_set_3_upload

 

可以看到跟ＭＤＷ有关的job已经被移除。另外提一下在2012中微软提供了一个新的系统存储过程sp_syscollector_delete_collection_set，可以删除自定义的MDW job和对象，其实看下这个存储过程的代码跟上面的差不多。

 

CREATEPROC [dbo].[sp_syscollector_cleanup_collector]

@collection_set_id INT=NULL

AS

BEGIN

IF (@collection_set_id ISNOTNULL)

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)

SAVETRANSACTION tran_cleanup_collection_set

ELSE

BEGINTRANSACTION

 

BEGINTRY

-- changing isolation level to repeatable to avoid any conflicts that may happen

-- while running this stored procedure and sp_syscollector_start_collection_set concurrently

SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD

 

-- Security check (role membership)

IF (NOT(ISNULL(IS_MEMBER(N'dc_admin'), 0)= 1)ANDNOT(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 not interfere with cleanup process

ALTERTABLE dbo.syscollector_collection_sets_internal NOCHECKCONSTRAINT FK_syscollector_collection_sets_collection_sysjobs

ALTERTABLE dbo.syscollector_collection_sets_internal NOCHECKCONSTRAINT FK_syscollector_collection_sets_upload_sysjobs

 

-- Delete data collector jobs

DECLARE @job_id uniqueidentifier

DECLARE datacollector_jobs_cursor CURSORLOCAL

FOR

SELECT collection_job_id AS job_id FROMsyscollector_collection_sets

WHERE collection_job_id ISNOTNULL

AND( collection_set_id = @collection_set_id OR @collection_set_id ISNULL)

UNION

SELECT upload_job_id AS job_id FROMsyscollector_collection_sets

WHERE upload_job_id ISNOTNULL

AND( collection_set_id = @collection_set_id OR @collection_set_id ISNULL)

 

OPEN datacollector_jobs_cursor

FETCHNEXTFROM datacollector_jobs_cursor INTO @job_id

WHILE (@@fetch_status= 0)

BEGIN

IFEXISTS(SELECTCOUNT(job_id)FROM sysjobs WHERE job_id = @job_id )

BEGIN

DECLARE @job_name sysname

SELECT @job_name = name from sysjobs WHERE job_id = @job_id

PRINT'Removing job '+ @job_name

EXEC dbo.sp_delete_job@job_id=@job_id, @delete_unused_schedule=0

END

FETCHNEXTFROM datacollector_jobs_cursor INTO @job_id

END

CLOSE datacollector_jobs_cursor

DEALLOCATE datacollector_jobs_cursor

 

-- Enable Constraints back

ALTERTABLE dbo.syscollector_collection_sets_internal CHECKCONSTRAINT FK_syscollector_collection_sets_collection_sysjobs

ALTERTABLE dbo.syscollector_collection_sets_internal CHECKCONSTRAINT FK_syscollector_collection_sets_upload_sysjobs

 

 

-- Disable trigger on syscollector_collection_sets_internal

-- this is done to make sure that trigger logic does not interfere with cleanup process

EXEC('DISABLE TRIGGER syscollector_collection_set_is_running_update_trigger ON syscollector_collection_sets_internal')

 

-- Set collection sets as not running state and update collect 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 ISNULL)

 

-- Enable back trigger on syscollector_collection_sets_internal

EXEC('ENABLE TRIGGER syscollector_collection_set_is_running_update_trigger ON syscollector_collection_sets_internal')

 

-- re-set collector config store if there is no enabled collector

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

DELETEFROM syscollector_execution_log_internal

WHERE (collection_set_id = @collection_set_id OR @collection_set_id ISNULL)

 

IF (@TranCounter = 0)

BEGIN

COMMITTRANSACTION

END

RETURN(0)

ENDTRY

BEGINCATCH

IF (@TranCounter = 0 ORXACT_STATE()=-1)

ROLLBACKTRANSACTION

ELSEIF (XACT_STATE()= 1)

ROLLBACKTRANSACTION tran_cleanup_collection_set

 

DECLARE @ErrorMessageNVARCHAR(4000);

DECLARE @ErrorSeverityINT;

DECLARE @ErrorStateINT;

DECLARE @ErrorNumberINT;

DECLARE @ErrorLineINT;

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)

ENDCATCH

END