Manage Data Collection

 

Updated: July 18, 2016

Applies To: SQL Server 2016

Use SQL Server Management Studio or Transact-SQL stored procedures and functions to manage different aspects of data collection, such as enabling or disabling data collection, changing a collection set configuration, or viewing data in the management data warehouse.

Perform the following data collector-related tasks by using Object Explorer in SQL Server Management Studio:

The data collector provides an extensive collection of stored procedures that you can use to perform any data-collector related task. For example, by using Transact-SQL, you can perform the following tasks:

In addition, there are functions and views that you can use to get configuration data for the msdb and management data warehouse databases, execution log data, and data that is stored in the management data warehouse.

You can use the stored procedures, functions, and views that are provided to create your own end-to-end data collection scenarios.

IMPORTANT!! Unlike regular stored procedures, the data collector stored procedures use strictly typed parameters and do not support automatic data type conversion. If these parameters are not called with the correct input parameter data types, as specified in the argument description, the stored procedure returns an error.

Use SQL Server Management Studio to create and execute the provided code samples. For more information, see Object Explorer. As an alternative you can create the query in any editor and save it in a text file that has a .sql file name extension. You can execute the query from the Windows command prompt using the sqlcmd utility. For more information, see Use the sqlcmd Utility.

Stored Procedures and Views

Working with the data collector

The following table describes the stored procedures that you can use to work with the data collector.

Procedure nameDescription
sp_syscollector_enable_collectorEnable the data collector.
sp_syscollector_disable_collectorDisable the data collector.

Working with collection sets

The following table describes the stored procedures that you can use to work with collection sets.

Procedure nameDescription
sp_syscollector_run_collection_set (Transact-SQL)Run a collection set on demand.
sp_syscollector_start_collection_set (Transact-SQL)Start a collection set.
sp_syscollector_stop_collection_set (Transact-SQL)Stop a collection set.
sp_syscollector_create_collection_set (Transact-SQL)Create a collection set.
sp_syscollector_delete_collection_set (Transact-SQL)Delete a collection set.
sp_syscollector_update_collection_set (Transact-SQL)Change a collection set configuration.
sp_syscollector_upload_collection_set (Transact-SQL)Upload collection set data to the management data warehouse. This is effectively an on-demand upload.

Working with collection items

The following table describes the stored procedures that you can use to work with collection items.

Procedure nameDescription
sp_syscollector_create_collection_item (Transact-SQL)Create a collection item.
sp_syscollector_delete_collection_item (Transact-SQL)Delete a collection item.
sp_syscollector_update_collection_item (Transact-SQL)Update a collection item.

Working with collector types

The following table describes the stored procedures that you can use to work with collector types.

Procedure nameDescription
sp_syscollector_create_collector_type (Transact-SQL)Create a collector type.
sp_syscollector_update_collector_type (Transact-SQL)Update a collector type.
sp_syscollector_delete_collector_type (Transact-SQL)Delete a collector type.

Getting configuration information

The following table describes the views that you can use for getting configuration information and execution log data.

View nameDescription
syscollector_config_store (Transact-SQL)Get data collector configuration.
syscollector_collection_items (Transact-SQL)Get collection item information.
syscollector_collection_sets (Transact-SQL)Get collection set information.
syscollector_collector_types (Transact-SQL)Get collector type information.
syscollector_execution_log (Transact-SQL)Get information about collection set and package execution.
syscollector_execution_stats (Transact-SQL)Get information about task execution.
syscollector_execution_log_full (Transact-SQL)Get information when the execution log is full.

Configuring access to the management data warehouse

The following table describes the stored procedures that you can use to configure access to the management data warehouse.

Procedure nameDescription
sp_syscollector_set_warehouse_database_name (Transact-SQL)Specify the database name defined in the connection string for the management data warehouse.
sp_syscollector_set_warehouse_instance_name (Transact-SQL)Specify the instance defined in the connection string for the management data warehouse.

Configuring the management data warehouse

The following table describes the stored procedures that you can use to work with the management data warehouse configuration.

Procedure nameDescription
core.sp_create_snapshot (Transact-SQL)Create a collection snapshot in the management data warehouse.
core.sp_update_data_source (Transact-SQL)Update the data source for data collection.
core.sp_add_collector_type (Transact-SQL)Add a collector type to the management data warehouse.
core.sp_remove_collector_type (Transact-SQL)Remove a collector type from the management data warehouse.
core.sp_purge_data (Transact-SQL)Delete data from the management data warehouse.

Working with upload packages

The following table describes the stored procedures that you can use to work with upload packages.

Procedure nameDescription
sp_syscollector_set_cache_window (Transact-SQL)Configure the number of data upload retries.
sp_syscollector_set_cache_directory (Transact-SQL)Specify temporary storage for data between upload retries.

Working with the data collection execution log

The following table describes the stored procedures that you can use to work with the data collection execution log.

Procedure nameDescription
sp_syscollector_delete_execution_log_tree (Transact-SQL)Delete collection set entries from the execution log.

Functions

The following table describes the functions that you can use to obtain execution and trace information.

Function nameDescription
fn_syscollector_get_execution_details (Transact-SQL)Get SSIS execution log data for a specific package.
fn_syscollector_get_execution_stats (Transact-SQL)Get execution statistics for a collection set or package. This information includes errors that are logged.
snapshots.fn_trace_getdata (Transact-SQL)Get the events that are logged when the Generic SQL Trace collector type is used to collect data.

Execute a Stored Procedure
Use SQL Server Management Studio
Data Collection

Community Additions

ADD
Show: