关于Data Collector只能禁用，且不能删除其job的问题

 

http://connect.microsoft.com/SQLServer/feedback/details/334180/data-collector-remove-data-collector-to-remove-associated-objects

 

After the data collector is configured, the data collector can be disabled but not removed. This means that all objects created by the data collector will remain on the instance and certain objects cannot be removed.

When the data collector is disabled, the associated SQL Server Agent jobs and system data collection sets cannot be removed. The jobs will appear as disabled, but SQL Server will not allow you to remove the jobs through T-SQL or through SSMS.

Database Administrators have no option to remove these jobs. If an environment chooses to stop using data collection in the future, they will always have to look around jobs which will never be used on the instance in the future.

Lara Rubbelke

由 Microsoft 在 2009/5/29 9:29 发送

Lara -

I've resolved this bug duplicate of the work item tracking this work for SQL11. We do plan to make the uninstall experience better for Data Collector in our next major release.

Thanks,

Amy Lewis

Updated this at 2012.07.30 
if in sql server 2008/2008R2:

http://blogs.msdn.com/b/sqlagent/archive/2011/07/22/remove-associated-data-collector-jobs.aspx

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

if in sql server 2012:

http://blogs.msdn.com/b/sqlagent/archive/2012/04/05/remove-associated-data-collector-jobs-in-sql-2012.aspx