Data Collections restart and clear cache files

/* uncomment and add to step 1 of SQL Agent job
-- stop default data collection sets

USE msdb;
GO
EXEC sp_syscollector_stop_collection_set @collection_set_id = 1;
EXEC sp_syscollector_stop_collection_set @collection_set_id = 2;
EXEC sp_syscollector_stop_collection_set @collection_set_id = 3;


*/ add this stored procedure as step 2 of SQL Agent job


USE MDW


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[uspDeleteCacheFiles] 
AS

/*****************************************************************************************

Name:uspDeleteCacheFiles
Description:-- called from SQL Agent job to restart SQLMDW data collections (!!! note enable XPCmdShell for file system access)
1. stop data collections
2. clear cache files (this procedure)
3. start data collections


Author:MattF
Creation Date:11-Sep-2018
Version:1.0

Parameters:- Input(s)

- Output(s)

Modification History:
------------------------------------------------------------------------------------------
Version  DateNameModification
------------------------------------------------------------------------------------------
v1.011-Sep-2018MattFInitial Version

*****************************************************************************************/SET NOCOUNT ON

BEGIN


DECLARE @CachePath varchar (250), @blnActive bit, @intStopped int, @SQLString nvarchar(MAX)
DECLARE @tblConfig TABLE (ParameterName sql_variant, ParameterValue sql_variant)

INSERT INTO @tblConfig
SELECT * FROM [msdb].[dbo].[syscollector_config_store_internal]

SELECT @CachePath =  CONVERT(varchar (250), ParameterValue) FROM @tblConfig
WHERE ParameterName = 'CacheDirectory'

SET @CachePath = NULLIF(LTRIM(RTRIM(@CachePath)), N'')

 -- Check if data collections are enabled
   
    EXEC @blnActive = [msdb].[dbo].[sp_syscollector_verify_collector_state] @desired_state = 1

    IF (@blnActive = 0)
    -- data collections are enabled

BEGIN

-- check that default collection sets are not running (1,2,3) - these should have been stopped by the calling SQL Agent job
SET @intStopped = (SELECT ISNULL(count(*),0) AS RunningCount
FROM msdb.dbo.syscollector_collection_sets
WHERE collection_set_id IN ('1','2','3') AND is_running = 0
GROUP BY is_running)


IF @intStopped = 3  -- all 3 collections sets are stopped
BEGIN
-- delete all files in cache location

SET @SQLString = 'EXEC master..xp_cmdshell ''' + 'del "' + @CachePath + '\*.cache"'''

--PRINT  @SQLString
EXECUTE sp_executesql @SQLString
END

END

END


/* uncomment and add to step 3 of SQL Agent job
-- start default data collection sets

USE msdb;
GO
EXEC sp_syscollector_start_collection_set @collection_set_id = 1;
EXEC sp_syscollector_start_collection_set @collection_set_id = 2;
EXEC sp_syscollector_start_collection_set @collection_set_id = 3;

*/