SQL2008：顽固的数据收集功能

jrgf

SQL2008 数据收集功效，是一个能够收集数据库服务器运行中的所有事务记载。但却要就义掉服务器硬件机能。
停用了又不能全体删除，这时你须要用到以下的脚步！

-----------------------------------------------
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
---------------------------------------
请把宰割线中的保留为sql的脚步文件，并履行即可。

qqruser

看帖回帖是美德！:lol

chunjihong

人生不能像做菜、把所有的料都准备好才下锅!

杨叔叔

帮帮顶顶！！

houbin

不在课堂上沉睡，就在酒桌上埋醉。

loyalxuan

所有刻骨铭心的爱都灵魂游离于床上的瞬间！

2004

如果跟导师讲不清楚，那么就把他搞胡涂吧!