sp_syscollector_set_cache_directory (Transact-SQL)

Specifies the directory where collected data is stored before it is uploaded to the management data warehouse.

Topic link iconTransact-SQL Syntax Conventions

sp_syscollector_set_cache_directory [ @cache_directory= ] 'cache_directory'

[ @cache_directory = ] 'cache_directory'

The directory in the file system where collected data is stored temporarily. cache_directory is nvarchar(255), with a default value of NULL. If no value is specified, the default temporary SQL Server directory is used.

0 (success) or 1 (failure)

You must disable the data collector before changing the cache directory configuration. This stored procedure fails if the data collector is enabled. For more information, see How to: Enable or Disable Data Collection, and Managing Data Collection Using Transact-SQL.

The specified directory does not need to exist at the time the sp_syscollector_set_cache_directory is executed; however, data cannot be successully cached and uploaded until the directory is created. We recommend creating the directory before executing this stored procedure.

Requires membership in the dc_admin (with EXECUTE permission) fixed database role to execute this procedure.

The following example disables the data collector, sets the cache directory for the data collector to D:\tempdata,and then enables the data collector.

USE msdb;
EXECUTE dbo.sp_syscollector_disable_collector;
EXEC dbo.sp_syscollector_set_cache_directory N'D:\tempdata';
EXECUTE dbo.sp_syscollector_enable_collector;

Community Additions