Heim >Datenbank >MySQL-Tutorial >删除ManagementDataWarehouse(MDW)job失败

删除ManagementDataWarehouse(MDW)job失败

WBOY
WBOYOriginal
2016-06-07 15:23:071051Durchsuche

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

可以看到跟MDW有关的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 

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel:xtrabackup用法【备忘】Nächster Artikel:归档日志管理