當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

删除Management Data Warehouse (MDW) job失败

發布時間：2023/12/14 编程问答 23 豆豆

生活随笔收集整理的這篇文章主要介紹了删除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

-- 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

運行之后會看到下面的結果：

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

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

總結

以上是生活随笔為你收集整理的删除Management Data Warehouse (MDW) job失败的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。