Data Collection, Puppy Style

Update: Based on Bill Ramos’ comment below and a note on Twitter (thanks!!) I have added some more details which hopefully make the situation clearer.

Summary

When setting up data collection using the GUI, configuration failed halfway through setup. At the point  of failure I found there were SQL Agent jobs for collections visible, but nothing appearing  in Management Studio under Management\Data Collection. The Data Collection icon still had the ‘off’ down red arrow. (I manually refreshed it for good measure to make sure.)

Beware: this isn’t an officially endorsed workaround. It’s just how I found I could make it work. I am testing this on a server which isn’t in production use.

My Setup

I was configuring the data collector on a SQL 2008 R2 instance (clustered), and pointing to a SQL 2008 (NOT R2) Management Data Warehouse. The MDW is on another server of course– that one isn’t clustered (although I don’t think it matters).  Since the MDW server isn’t R2 it  doesn’t (and can’t) have a Utility Control Point installed, but when you set up data collection on an R2 target, it automatically includes some of the collection sets for the UCP. That’s fine, they should just stay off when they don’t communicate with a UCP.

In the past I have successfully configured Data Collection at least 10 times without seeing this issue, however at that point my Data Collection targets and my Management Data Warehouse were all on SQL Server 2008. This is the first time I have configured data collection on a SQL Server 2008 R2 target using a MDW which is SQL Server 2008. Basically, I am bringing this R2  box up for performance testing and burn-in now, and later on will be doing a separate change to bring the MDW up to SQL Server 2008 R2.

The Point of Failure

Setting up data collection with the GUI failed half way through. Everything was fine walking through the GUI, but when I told it to set up collection it failed halfway through the setup process and could not complete.

The failure report showed the following error [excerpted]:

TITLE: Microsoft.SqlServer.Management.Collector

——————————

Unable to start collection set Utility Information.

....

Caught error#: 14693, Level: 16, State: 1, in Procedure: sp_syscollector_start_collection_set, Line: 108, with Message: A collection set cannot start without a schedule. Specify a schedule for the collection set. (Microsoft SQL Server, Error: 14684)


Looking at the system configuration, jobs had been created in the SQL agent for data collection, but no system collections were visible under the Data Collector in management studio.

At this point I was not able to uninstall and reinstall– currently Data Collection can be disabled, but it can’t be uninstalled.

The Error Message is Helpful!

As far as errors go, it told me exactly what the problem was, which I appreciate. Thank you error handling!

I reran the data collector setup for good measure to see if I got the same result the second time, and I did. I did a quick search on the internetz to see if anyone else had found a solution and didn’t see one. I checked Microsoft  Connect (https://connect.microsoft.com/SQLServer) and didn’t find anything there either.

I queried the collection sets in msdb with the following  query and verified that the ‘Utility Information’ collection did have null for the schedule_id.

SELECT * FROM msdb.dbo.syscollector_collection_sets

I went ahead and ran some a little sql trace while reproducing the issue just to take a look at what it was doing. I found that it wasn’t trying to set the schedule at all for the collection.

I checked through the stored procedure for data collection and found that there is one which updates the schedule. I ran the following script to update the ‘Utility Information’ collection so it is on the same schedule as the ‘Query Statistics’ collection set  (collection_set_id=3)

declare @schedule_uid uniqueidentifier
SELECT @schedule_uid =schedule_uid FROM dbo.syscollector_collection_sets WHERE collection_set_id=3
 
exec dbo.sp_syscollector_update_collection_set
@collection_set_id=4
, @schedule_uid=@schedule_uid

I then re-ran setup and it was able to complete successfully. I then stopped the ‘Utility Information’ collection set and configured it to be ‘On Demand’ (thereby removing the schedule).

So, at least on the surface, it appears I did no harm since I temporarily enabled a schedule, when I then removed. (I verified the schedule_uid field is once again set to null for collection_set_id=4). But there’s really no guarantees, of course.  Since this server isn’t in production use right now and I’m working with it closely to evaluate it, I have time to see how things go.

Follow Up – Filed a Connect Bug

So it was Friday night and I’m a bit tired, but I figured I should do the right thing and file a Connect bug.

This is connect bug 571046.