Export (0) Print
Expand All

sp_syscollector_set_cache_directory (Transact-SQL)


Applies To: SQL Server 2014, SQL Server 2016 Preview

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

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-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 Enable or Disable Data Collection, and Manage Data Collection.

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;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft