Data Collector

Overview

The data collector is a feature most instances never switch on and a few depend on entirely. When it is on, it runs packages on a schedule, writes into a cache, and uploads into a management data warehouse that is usually on another server.

When it goes wrong, it goes wrong quietly. The collection keeps running, the upload keeps failing, the cache keeps growing, and the warehouse somebody is reporting from stops moving without anybody being told.

The Data Collector page: what each collection set is doing, and which half is broken
The whole page on an instance where the collector has never been started. Four collection sets, none running, no warehouse configured.

Where to find it

Expand a server in the tree, expand the msdb database, then MSDB → Data Collector.

Shown only on SQL Server 2008 and newer, and only on an instance that can have SQL Server Agent.

Three things this page can say and nothing else on the instance can

Whether each collection set is running, and which jobs run it

The two jobs are separate. Each collection set has a collection job and an upload job, and an instance where collection works and upload does not is the common failure. A page that showed one job per set would show a healthy one and hide the broken one.

The last upload, and the failure text

syscollector_execution_log keeps a failure_message and nothing reads it. That text is usually the whole answer: a warehouse that has moved, a login that has expired, a database that is full.

Where the warehouse actually is

The config store holds the instance and database name. This is the question somebody always has to go and find out by hand, and it is one row.

Reading the chart

The bars: failures where there are any, runs where there are not
Four sets, none of which has ever run. The state and the retention sit under each name, so a set that keeps 730 days of something it never collects is visible without opening the grid.

One bar per collection set. Where a set has failures the bar is the failure count, and where it has none the bar is the run count, so the picture is of whichever of the two there is something to say about.

An instance that has never switched the collector on draws empty bars labeled never run, which is the answer rather than a blank panel.

Reading the grid

The grid: state, items, runs, failures and the failure text
The verdict separates the two halves of each set: a stopped set collects nothing, and a cached set with no upload job collects into a cache that never reaches a warehouse.
Column What it is
Collection set The set, by its name.
State Running, stopped, or never started, with the collection mode (cached or non-cached).
Items How many collection items the set contains.
Runs Executions recorded in the log.
Failures How many of those failed.
Last run When it last ran.
Newest failure The failure_message text, in full.
Verdict Whether this set is collecting, whether it is uploading, and which of the two is broken.

The toolbar

Control What it does
Agent activity Opens Agent Activity, where the collection and upload jobs appear.
Step failures Opens Job Step Failures, where the job side of a failed upload is recorded.
Space and retention Opens msdb Space and Retention, where a cache that is not uploading shows as growth.

Where the data comes from

Source What it gives
dbo.syscollector_collection_sets The sets, their state, their collection mode and their two job ids.
dbo.syscollector_collection_items How many items each set collects.
dbo.syscollector_execution_log Every run, its status, its duration and the failure message.
dbo.syscollector_execution_log_full The same log with the operator text resolved.
dbo.syscollector_config_store The warehouse instance and database, and whether the collector is enabled at all.
dbo.sysjobs The names of the collection and upload jobs.

Messages you may see

No collection sets are defined on this instance. The data collector has never been configured here. The views exist on every instance from SQL Server 2008 onward, which is why this page can say so rather than failing.

The data collector arrived in SQL Server 2008. This instance is older, so the syscollector views this page reads are not there at all.

Report Why you would go there
Agent Activity The collection and upload jobs, on the Agent timeline.
Job Step Failures What the upload job’s step said when it failed.
msdb Space and Retention The cache that grows when the upload stops.
Job Commands What the collector’s job steps actually run.

Frequently asked questions

Collection is running and the warehouse has not moved in weeks. That is the upload job, not the collection job. The Newest failure column usually names the reason outright.

Where is the management data warehouse? On this page, in the summary line above the grid. It comes out of the config store rather than out of anybody’s notes.

The collector is off. Should I turn it on? That is a separate decision and this page does not make it. What it does say is whether the feature is on and failing, which is the state worth knowing about.

We removed the collector and msdb is still large. Disabling the collector does not remove what it cached. msdb Space and Retention shows what is left.