Management Data Warehouse Collection Failures

I recently discovered that the Query Statistics and Server Activity collection sets were no longer working in the Management Data Warehouse on a SQL 2012 server. Both the SQL Agent Jobs and the Data Collection Sets seemed to be enabled and running but no data was being loaded. I disabled and re-enabled the collection sets several times, but that didn’t help.

The first clue as to the source of the issue came from the collection set log, which can be found by navigating to Management > Data Collection > System Data Collection Sets > Right clicking on Server Activity (or Query Statistics) and selecting View Logs. This log showed Success for the upload job, but every time the collection job ran, the following error message appeared in the log:

Failed to create kernel event for collection set: {GUID}. Inner Error ————-> Cannot create a file when that file already exists.

The source of the problem turned out to be dcexec.exe components that were running on the server from a previous launch of the data collection process. Dcexec.exe is the data collector run-time component that launches when a non-cached collection set is launched.

On a default configuration of the Management Data Warehouse, the Query Statistics and Server Activity collection sets are non-cached (Disk Usage is cached). In this case, there should be two instances of dcexec.exe running on the server. In the Task Manager, these are displayed as Background processes called Data Collector Execution Utility.

On my server, there were five instances of these processes running. This was a live production server, so before doing anything, I verified that the only thing that uses these processes is the Management Data Warehouse. Since that already wasn’t working, I felt it was safe to kill these processes on the production server.

In SSMS, I stopped all of the data collection sets. In Task Manager on the server, I ended all of the Data Collector Execution Utility tasks. Then, in SSMS, I started all of the data collection sets. They immediately started working.

This is a great source of information about how the Data Collector works under the hood:

Microsoft Technet Data Collector Architecture and Processing

 

 

Management Data Warehouse Collection Failures

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s