syscollector_config_store (Transact-SQL)


THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns properties that apply to the entire data collector, as opposed to a collection set instance. Each row in this view describes a specific data collector property, such as the name of the management data warehouse, and the instance name where the management data warehouse is located.

Applies to: SQL Server (SQL Server 2008 through current version).
Column nameData typeDescription
parameter_namenvarchar(128)The name of the property. Is not nullable.
parameter_valuesql_variantThe actual value of the property. Is nullable.

Requires SELECT permission on the view or membership in the dc_operator, dc_proxy, or dc_admin fixed database roles.

The list of properties available is fixed and their values can only be changed using the appropriate stored procedure. The following table describes the properties that are exposed through this view.

Property nameDescription
CacheDirectoryThe name of the directory in the file system where the collector type packages store temporary information.

NULL = the default temporary SQL Server directory is used.
CacheWindowIndicates the data retention policy of the cache directory for failed data uploads.

-1 = Retain the data from all upload failures.

0 = Do not retain any data from upload failures.

 n = Retain data from n previous upload failures, where n >= 1.

Use the sp_syscollector_set_cache_window stored procedure to change this value.
CollectorEnabledIndicates the state of the data collector.

0 = disabled

1 = enabled

Use either the sp_syscollector_enable_collector or sp_syscollector_disable_collector stored procedure to change this value.
MDWDatabaseThe name of the management data warehouse. Use the sp_syscollector_set_warehouse_database_name stored procedure to change this value.
MDWInstanceThe name of the SQL Server instance for the management data warehouse. Use the sp_syscollector_set_warehouse_instance_name stored procedure to change this value.

The following example queries the syscollector_config_store view.

SELECT parameter_name, parameter_value  
FROM msdb.dbo.syscollector_config_store;  

Data Collector Stored Procedures (Transact-SQL)
Data Collector Views (Transact-SQL)
Data Collection
sp_syscollector_enable_collector (Transact-SQL)
sp_syscollector_disable_collector (Transact-SQL)
sp_syscollector_set_warehouse_database_name (Transact-SQL)
sp_syscollector_set_warehouse_instance_name (Transact-SQL)
sp_syscollector_set_cache_window (Transact-SQL)

Community Additions