An error occurred while deleting the ManagementDataWarehouse (MDW) job.

Recently, I have been clearing unnecessary jobs and found several MDW-related jobs. Although the Job has been Disable, it has not been deleted. The following error occurs when you try to delete a file: Deleted

Recently, I have been clearing unnecessary jobs and found several MDW-related jobs. Although the Job has been Disable, it has not been deleted. The following error occurs when you try to DELETE the file: The DELETE statement conflicted with The REFERENCE constraintFK_syscollector_collection_sets_collection_sysjobs. the conflictoccurr

Recently, I have been clearing unnecessary jobs and found several MDW-related jobs. Although the Job has been Disable, it has not been deleted. The following error occurs when you try to delete a file:

The DELETE statement conflicted with the REFERENCE constraint "statement". The conflictoccurred in database "msdb", table "dbo. syscollector_collection_sets_internal", column 'collection _ job_id '.

The statement has been terminated. (. Net SqlClient Data Provider)

I checked some documents and found that this problem exists in 2008/2008 R2. It can only be Disable but cannot be deleted. I found an article named Removeassociated data collector jobs, which provides code to delete jobs and objects related to MDW. although this script can only be run in the test environment, it does not affect other applications.

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

The following result is displayed after running:

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

The job related to MDW has been removed. In addition, Microsoft provided a new system stored procedure sp_syscollector_delete_collection_set in 2012 to delete custom MDW jobs and objects. In fact, the code of this stored procedure is similar to above.

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