• Article

Managing Data Collection Using Transact-SQL

The data collector provides an extensive collection of stored procedures that you can use to perform any data collection task. 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.

You can use SQL Server Management Studio to create and execute the provided code samples. For more information, see Using 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 Using 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 name

Description

sp_syscollector_enable_collector

Enable the data collector.

sp_syscollector_disable_collector

Disable the data collector.

Working 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 (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 name

Description

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 name

Description

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 name

Description

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 name

Description

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 name

Description

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 name

Description

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 name

Description

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 name

Description

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_gettable (Transact-SQL)

Get the events that are logged when the Generic SQL Trace collector type is used to collect data.