删除Management Data Warehouse (MDW) job失败

转：http://lzf328.blog.51cto.com/1196996/1349670

最近在清理一些不用的Job,发现几个跟MDW有关的。虽然Job已经被Disable, 但是没有被删除。尝试删除出现下面的错误：

The DELETE statement conflicted with the REFERENCE constraint "FK_syscollector_collection_sets_collection_sysjobs". The conflict occurred 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但是无法删除。找到了一篇文章Remove associated data collector jobs提供了代码去删除MDW的相关job和对象.虽然文章提到只能在测试环境中运行这个脚本，但是这个脚本不会对其他应用产生影响。

USE MSDB
GO
-- Disable constraints ALTER TABLE dbo.syscollector_collection_sets_internal NOCHECK CONSTRAINT FK_syscollector_collection_sets_collection_sysjobs
ALTER TABLE dbo.syscollector_collection_sets_internal NOCHECK CONSTRAINT FK_syscollector_collection_sets_upload_sysjobs
  
-- Delete data collector jobs
DECLARE @job_id uniqueidentifier
DECLARE datacollector_jobs_cursor CURSOR LOCAL
FOR
SELECT collection_job_id AS job_id FROM syscollector_collection_sets
WHERE collection_job_id IS NOT NULL
UNION
SELECT upload_job_id AS job_id FROM syscollector_collection_sets
WHERE upload_job_id IS NOT 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 WHERE job_id = @job_id )
BEGIN
DECLARE @job_name sysname
SELECT @job_name = name from sysjobs WHERE job_id = @job_id
PRINT 'Removing job '+ @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 CONSTRAINT FK_syscollector_collection_sets_collection_sysjobs
ALTER TABLE dbo.syscollector_collection_sets_internal CHECK CONSTRAINT FK_syscollector_collection_sets_upload_sysjobs
  
-- Disable trigger on syscollector_collection_sets_internal
EXEC('DISABLE TRIGGER syscollector_collection_set_is_running_update_trigger ON syscollector_collection_sets_internal')
  
-- Set collection sets as not running state
UPDATE syscollector_collection_sets_internal
SET is_running = 0
  
-- Update collect and upload jobs as null
UPDATE syscollector_collection_sets_internal
SET collection_job_id =NULL, upload_job_id =NULL
  
-- Enable back trigger on syscollector_collection_sets_internal
EXEC('ENABLE TRIGGER syscollector_collection_set_is_running_update_trigger ON syscollector_collection_sets_internal')
  
-- re-set collector 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')
  
-- Delete collection set logs
DELETE FROM syscollector_execution_log_internal
  
GO

运行之后会看到下面的结果：

Removing job collection_set_3_collection

Removing job collection_set_2_upload

Removing job collection_set_1_noncached_collect_and_upload

Removing job collection_set_2_collection

Removing job collection_set_3_upload

可以看到跟ＭＤＷ有关的job已经被移除。另外提一下在2012中微软提供了一个新的系统存储过程sp_syscollector_delete_collection_set，可以删除自定义的MDW job和对象，其实看下这个存储过程的代码跟上面的差不多。

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

CREATEPROC [dbo].[sp_syscollector_cleanup_collector]
@collection_set_id INT=NULL
AS
BEGIN
IF (@collection_set_id ISNOTNULL)
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)
SAVETRANSACTION tran_cleanup_collection_set
ELSE
BEGINTRANSACTION
  
BEGINTRY
-- changing isolation level to repeatable to avoid any conflicts that may happen
-- while running this stored procedure and sp_syscollector_start_collection_set concurrently
SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD
  
-- Security check (role membership)
IF (NOT(ISNULL(IS_MEMBER(N'dc_admin'), 0)= 1)ANDNOT(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 not interfere with cleanup process
ALTERTABLE dbo.syscollector_collection_sets_internal NOCHECKCONSTRAINT FK_syscollector_collection_sets_collection_sysjobs
ALTERTABLE dbo.syscollector_collection_sets_internal NOCHECKCONSTRAINT FK_syscollector_collection_sets_upload_sysjobs
  
-- Delete data collector jobs
DECLARE @job_id uniqueidentifier
DECLARE datacollector_jobs_cursor CURSORLOCAL
FOR
SELECT collection_job_id AS job_id FROMsyscollector_collection_sets
WHERE collection_job_id ISNOTNULL
AND( collection_set_id = @collection_set_id OR @collection_set_id ISNULL)
UNION
SELECT upload_job_id AS job_id FROMsyscollector_collection_sets
WHERE upload_job_id ISNOTNULL
AND( collection_set_id = @collection_set_id OR @collection_set_id ISNULL)
  
OPEN datacollector_jobs_cursor
FETCHNEXTFROM datacollector_jobs_cursor INTO @job_id
WHILE (@@fetch_status= 0)
BEGIN
IFEXISTS(SELECTCOUNT(job_id)FROM sysjobs WHERE job_id = @job_id )
BEGIN
DECLARE @job_name sysname
SELECT @job_name = name from sysjobs WHERE job_id = @job_id
PRINT'Removing job '+ @job_name
EXEC dbo.sp_delete_job@job_id=@job_id, @delete_unused_schedule=0
END
FETCHNEXTFROM datacollector_jobs_cursor INTO @job_id
END
CLOSE datacollector_jobs_cursor
DEALLOCATE datacollector_jobs_cursor
  
-- Enable Constraints back
ALTERTABLE dbo.syscollector_collection_sets_internal CHECKCONSTRAINT FK_syscollector_collection_sets_collection_sysjobs
ALTERTABLE dbo.syscollector_collection_sets_internal CHECKCONSTRAINT FK_syscollector_collection_sets_upload_sysjobs
  
  
-- Disable trigger on syscollector_collection_sets_internal
-- this is done to make sure that trigger logic does not interfere with cleanup process
EXEC('DISABLE TRIGGER syscollector_collection_set_is_running_update_trigger ON syscollector_collection_sets_internal')
  
-- Set collection sets as not running state and update collect 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 ISNULL)
  
-- Enable back trigger on syscollector_collection_sets_internal
EXEC('ENABLE TRIGGER syscollector_collection_set_is_running_update_trigger ON syscollector_collection_sets_internal')
  
-- re-set collector config store if there is no enabled collector
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
DELETEFROM syscollector_execution_log_internal
WHERE (collection_set_id = @collection_set_id OR @collection_set_id ISNULL)
  
IF (@TranCounter = 0)
BEGIN
COMMITTRANSACTION
END
RETURN(0)
ENDTRY
BEGINCATCH
IF (@TranCounter = 0 ORXACT_STATE()=-1)
ROLLBACKTRANSACTION
ELSEIF (XACT_STATE()= 1)
ROLLBACKTRANSACTION tran_cleanup_collection_set
  
DECLARE @ErrorMessageNVARCHAR(4000);
DECLARE @ErrorSeverityINT;
DECLARE @ErrorStateINT;
DECLARE @ErrorNumberINT;
DECLARE @ErrorLineINT;
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)
ENDCATCH
END