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

Syntax

sp_syscollector_set_cache_directory [ @cache_directory= ] 'cache_directory'

Arguments

  • [ @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.

Return Code Values

0 (success) or 1 (failure)

Remarks

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.

Permissions

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

Examples

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;
GO
EXECUTE dbo.sp_syscollector_disable_collector;
GO
EXEC dbo.sp_syscollector_set_cache_directory N'D:\tempdata';
GO
EXECUTE dbo.sp_syscollector_enable_collector;
GO