转：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和对象，其实看下这个存储过程的代码跟上面的差不多。

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

　　

最新文章

  1. [MySQL Reference Manual]14 InnoDB存储引擎
  2. Alpha、Beta、RC、GA版本的区别 ZT
  3. UNITY3D的变量初始化问题
  4. Java多线程之银行出纳员仿真
  5. WPF Application 执行顺序
  6. 基于opencv的车牌识别系统
  7. js里变量的作用域
  8. phpStudy2018安装与配置步骤详解
  9. java网络编程学习之NIO模型
  10. sqoop的安装
  11. Unity3D热更新之LuaFramework篇[02]--用Lua创建自己的面板
  12. SpringBoot-06：SpringBoot增删改查一套完整的考试案例
  13. 从Win32程序中的主函数中获取命令行参数
  14. TCP/IP学习20180629-数据链路层-ARP、IP
  15. Scala中()与{}
  16. Oracle性能优化之表压缩及并行提高效率的测试
  17. Linux 应用笔记
  18. JSR规范整理
  19. Nginx安装过程
  20. JQuery中使用FormData异步提交数据和提交文件

热门文章

  1. USACO翻译：USACO 2014 US Open 三题
  2. Oracle ORA-01722: 无效数字 处理方法
  3. 使用rsync同步文件
  4. [IT新应用]家用NAS，自建“360云盘”
  5. Java简单类——多对多映射
  6. 索引中include的魅力（具有包含性列的索引） (转)
  7. IOS 绘图教程Quartz2D
  8. Ubuntu 14.04 编译安装 boost 1.58
  9. zx一篇让Java程序猿随时可以翻看的Oracle总结
  10. vim - Convert between hex and decimal