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 I