Redigera

Dela via


Manage data collection

Applies to: SQL Server

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.

Manage data collection using SSMS

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

Manage data collection using Transact-SQL

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 don't support automatic data type conversion. If these parameters aren't 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 sqlcmd - use the utility.

Stored procedures and views

The following section describes stored procedures and views you use to work with data collection in the Database Engine.

Work with the data collector

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

Procedure name Description
sp_syscollector_enable_collector Enable the data collector.
sp_syscollector_disable_collector Disable the data collector.

Work with collection sets

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

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

Work with collection items

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

Procedure name Description
sp_syscollector_create_collection_item Create a collection item.
sp_syscollector_delete_collection_item Delete a collection item.
sp_syscollector_update_collection_item Update a collection item.

Work with collector types

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

Procedure name Description
sp_syscollector_create_collector_type Create a collector type.
sp_syscollector_update_collector_type Update a collector type.
sp_syscollector_delete_collector_type Delete a collector type.

Get configuration information

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

View name Description
syscollector_config_store Get data collector configuration.
syscollector_collection_items Get collection item information.
syscollector_collection_sets Get collection set information.
syscollector_collector_types Get collector type information.
syscollector_execution_log Get information about collection set and package execution.
syscollector_execution_stats Get information about task execution.
syscollector_execution_log_full Get information when the execution log is full.

Configure 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 name Description
sp_syscollector_set_warehouse_database_name Specify the database name defined in the connection string for the management data warehouse.
sp_syscollector_set_warehouse_instance_name Specify the instance defined in the connection string for the management data warehouse.

Configure the management data warehouse

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

Procedure name Description
core.sp_create_snapshot Create a collection snapshot in the management data warehouse.
core.sp_update_data_source Update the data source for data collection.
core.sp_add_collector_type Add a collector type to the management data warehouse.
core.sp_remove_collector_type Remove a collector type from the management data warehouse.
core.sp_purge_data Delete data from the management data warehouse.

Work with upload packages

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

Procedure name Description
sp_syscollector_set_cache_window Configure the number of data upload retries.
sp_syscollector_set_cache_directory Specify temporary storage for data between upload retries.

Work 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 name Description
sp_syscollector_delete_execution_log_tree 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 name Description
fn_syscollector_get_execution_details Get SSIS execution log data for a specific package.
fn_syscollector_get_execution_stats Get execution statistics for a collection set or package. This information includes errors that are logged.
snapshots.fn_trace_getdata Get the events that are logged when the Generic SQL Trace collector type is used to collect data.