Error Configuring DataCollector: A collection set cannot start without a schedule

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
, @[email protected]_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.

, , ,

8 Responses to Error Configuring DataCollector: A collection set cannot start without a schedule

  1. Bill Ramos June 28, 2010 at 8:07 pm #

    Hi Kendra,
    The Utility Information collection set is “designed” to never be started – even when the Utility is configured. You simply need to ignore it.
    Cheers,
    Bill Ramos, Program Manager, Microsoft.

    • Kendra Little June 28, 2010 at 9:20 pm #

      Hi Bill,

      Thanks so much for the comment.

      I didn’t convey the issue very well in the post, so I’ve revised it and tried to add more detail. The configuration for the data collector was failing on me halfway through its setup process, so I had agent jobs visible, but nothing under data collections. I’ve set up data collection before and never had this issue, so it was definitely odd.

      I updated the post and connect bug to try to provide more detail and make it more clear.

      Since I was bringing this new server up and getting it ready for perf testing and baselining, I was very happy to get data collection set up, though– I love the feature.

  2. Bill Graziano October 21, 2010 at 9:20 am #

    Thanks! I had the same problem. This worked for me. I’ll vote up the bug in Connect.

  3. Ann November 18, 2010 at 8:43 pm #

    I had this same problem on SQL 2008 with service pack 2 and your fix worked for me as well.

  4. Jon March 16, 2011 at 9:31 am #

    Very helpful post. I had the same issue with R2, and your post helped me fix the issue in just a few minutes. Thanks a ton!

  5. Cynthia Alvarez March 29, 2011 at 11:08 am #

    Thanks for posting your solution. It works great!

  6. Najm May 30, 2012 at 10:16 am #

    Kendra,
    Thanks for the post and work around. It did the trick.

Trackbacks/Pingbacks

  1. Four More Syndicated Bloggers at SQLServerPedia | Brent Ozar - Too Much Information - July 1, 2010

    […] for server health.  In the midst of all that, she’s a frequent Twitterer and blogger:Error Configuring DataCollector – she ran into problems configuring the Management Data Warehouse, then not only took the […]

Leave a Reply