删除Management Data Warehouse (MDW) job失败

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