Skip to content

sp-syscollector-set-cache-directory-transact-sql.md

Latest commit

 

History

History
72 lines (51 loc) · 2.8 KB

sp-syscollector-set-cache-directory-transact-sql.md

File metadata and controls

72 lines (51 loc) · 2.8 KB
title sp_syscollector_set_cache_directory (Transact-SQL)
description Specifies the directory where collected data is stored before it is uploaded to the management data warehouse.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sp_syscollector_set_cache_directory_TSQL
sp_syscollector_set_cache_directory
helpviewer_keywords
data collector [SQL Server], stored procedures
sp_syscollector_set_cache_directory stored procedure
dev_langs
TSQL

sp_syscollector_set_cache_directory (Transact-SQL)

[!INCLUDE SQL Server]

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

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_syscollector_set_cache_directory [ [ @cache_directory = ] N'cache_directory' ]
[ ; ]

The directory in the file system where collected data is stored temporarily. @cache_directory is nvarchar(255), with a default of an empty string. If no value is specified, the default temporary [!INCLUDE ssNoVersion] 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 Enable or disable data collection, and Manage data collection.

The specified directory doesn't need to exist at the time the sp_syscollector_set_cache_directory is executed; however, data can't be successfully 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

EXECUTE dbo.sp_syscollector_set_cache_directory @cache_directory = N'D:\tempdata';
GO

EXECUTE dbo.sp_syscollector_enable_collector;
GO

Related content