Start or Stop a Collection Set

This topic describes how to start or stop a collection set in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

• Before you begin:

  Limitations and Restrictions

  Prerequisites

  Recommendations

  Security

• To start or stop a collection set, using:

  SQL Server Management Studio

  Transact-SQL

Before You Begin

Limitations and Restrictions

• Data Collector stored procedures and catalog views are stored in the msdb database.

• Unlike regular stored procedures, the parameters for data collector stored procedures are strictly typed 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.

Prerequisites

• SQL Server Agent must be started.

Recommendations

• To obtain information about collection sets, query the syscollector_collection_sets catalog view.

Security

Permissions

Requires membership in the dc_operator fixed database role. If the collection set does not have a proxy account, membership in the sysadmin fixed server role is required.Examples

[Top]

Using SQL Server Management Studio

To start a collection set

1. In Object Explorer, expand the Management node, expand Data Collection, and then expand System Data Collection Sets.

2. Right-click the collection set that you want to start, and then click Start Data Collection Set.

   A message box displays the results of this action, and a green arrow on the icon for the collection set indicates that the collection set has started.

To stop a collection set

1. In Object Explorer, expand the Management node, expand Data Collection, and then expand System Data Collection Sets.

2. Right-click the collection set that you want to stop, and then click Stop Data Collection Set.

   A message box displays the results of this action, and a red circle on the icon for the collection set indicates that the collection set has stopped.

[Top]

Using Transact-SQL

To start a collection set

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. This example uses sp_syscollector_start_collection_set to start the collection set that has the ID of 1.

USE msdb;
GO
EXEC sp_syscollector_start_collection_set @collection_set_id = 1;

To stop a collection set

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. This example uses sp_syscollector_stop_collection_set to stop the collection set that has the ID of 1.

USE msdb;
GO
EXEC sp_syscollector_stop_collection_set @collection_set_id = 1;

[Top]

See Also

Reference

Data Collector Views (Transact-SQL)

Concepts

Data Collection