Manage Data Collection

You can 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 by Using SQL Server Management Studio

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

• Configure the Management Data Warehouse (SQL Server Management Studio)

• Enable or Disable Data Collection

• Start or Stop a Collection Set

• Use SQL Server Profiler to Create a SQL Trace Collection Set (SQL Server Management Studio)

• View Collection Set Logs (SQL Server Management Studio)

• View or Change Collection Set Schedules (SQL Server Management Studio)

• View a Collection Set Report (SQL Server Management Studio)

Manage Data Collection by 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:

• Configure Data Collection Parameters (Transact-SQL)

• Enable or Disable Data Collection

• Start or Stop a Collection Set

• Create a Custom Collection Set That Uses the Generic T-SQL Query Collector Type (Transact-SQL)

• Add a Collection Item to a Collection Set (Transact-SQL)

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 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 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_getdata (Transact-SQL)	Get the events that are logged when the Generic SQL Trace collector type is used to collect data.

See Also

Concepts

Execute a Stored Procedure

Data Collection

Other Resources

Use SQL Server Management Studio