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:
- Configure the management data warehouse (SQL Server Management Studio)
- Configure properties of a data collector
- Enable or disable data collection
- Start or stop a collection set
- Use SQL Server Profiler to create a SQL Trace collection set
- 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 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 custom collection set - Generic T-SQL Query collector type
- 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 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. |
Related content
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for