删除ManagementDataWarehouse(MDW)job失败

最近在清理一些不用的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