2

In SSMS of SQL Server 2012 Enterprise on Windows Server R2 Standard, each time I am trying to set sub-option "On demand" of option "Non-cached - Collect and upload data on the same schedule" of Data Collection Set, I am getting the error:

A collection set cannot start without a schedule. Specify a schedule for the collection set

enter image description here

TITLE: Microsoft SQL Server Management Studio

Operation 'Alter' on object 'Disk Usage' failed during execution. (Microsoft.SqlServer.Management.Sdk.Sfc)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Caught error#: 14684, Level: 16, State: 1, in Procedure:
sp_syscollector_start_collection_set, Line: 203, with Message: 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)

Now, I do not see how to re-setup or reconfigure the Data Collection in any way other than to enable and disable already existing malfunctioning one.

Any ideas how to make it working?

First of all, I'd like to understand whether it is me who screwed it (though I tried on different machines) or it is someone else ?

Update:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

marc_s
9,0626 gold badges46 silver badges52 bronze badges
asked Nov 28, 2013 at 7:55
0

2 Answers 2

5

It's a bug. This Connect ticket has a workaround for it from Kendra Little:

I was able to work around this issue by temporarily adding a scheduled to the Utility Information collection (@collection_set_id=4) with the SQL command below. After doing this I was able to complete the data collection configuration via the GUI successfully. Afterwards I went back and set the Utility Information collection to manual start, thereby removing the schedule.

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
Mark Storey-Smith
31.9k9 gold badges91 silver badges125 bronze badges
answered Nov 29, 2013 at 1:57
1

Added a variable as Utility information not always has ID 4 and msdb

use msdb
go
declare @schedule_uid uniqueidentifier,@collection_set_id int; 
select @schedule_uid =schedule_uid from dbo.syscollector_collection_sets 
where collection_set_id=3;
select @collection_set_id = collection_set_id from dbo.syscollector_collection_sets
where name = 'Utility Information';
exec dbo.sp_syscollector_update_collection_set
 @collection_set_id=@collection_set_id
 , @schedule_uid=@schedule_uid;
ypercubeTM
99.7k13 gold badges217 silver badges306 bronze badges
answered Jun 6, 2014 at 9:32
1
  • Can you clarify? Is this an improvement on @NickKavadias' answer? Commented Jun 6, 2014 at 10:00

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.