SQL Holmes: A Funny Thing Happened on My Way to Enable Management Data Warehouse

Three Job Schedules walk into a bar…

uh-oh.

Management Data Warehouse is a neat tool for collecting data on your instances with regards to performance, disk usage, and anything else related to SQL Server you might want to know. I’ve recently been rolling it out to various servers in test environments to get a good handle on all that it can do, and maybe more importantly, how it’s going to affect performance.

When I was satisfied that it was working the way I wanted in these environments, I started to push it to what you could call QA. Immediately I was hit with litany of errors, all of which said basically the same thing:

Caught error#: 14684, Level: 16, State: 1, in Procedure: sp_syscollector_create_collection_set, Line: 203, with Message: Caught error#: 14262, Level: 16, State: 1, in Procedure: sp_syscollector_create_collection_set, Line: 80, with Message: The specified @schedule_name ('CollectorSchedule_Every_15min') does not exist. (Microsoft SQL Server, Error: 14684)


Um, what? Of course it doesn’t exist, I’m installing it now. Just to verify, I did some poking around, and I found that it was indeed attempting to add a schedule that didn’t exist (as you would expect) to the collection set. After a while my Jr DBA (read: Google) turned up this article on removing MDW which says, in no uncertain terms:

The fact is that, while these schedules may look like they were created by MDW, they are created by default in a default installation of SQL Server 2008 or above. You can delete these easily, if you really want to, and if you are sure you will never enable MDW again. I would still suggest scripting the schedules and saving those scripts to your file system or source control so that you can recover them in the future.

-Microsoft Books-on-Tape-or-Something

I took a look back on the TEST instances where I was previously successful and found these schedules existed both on instances where MDW had been installed, and in places where it had not. These were part of the defaut SQL Server install and had been manually removed. Whoops.

As far as I know, there isn’t any direct way to script out schedules, so I could port them to the boxes where they had been removed. To get around this, I added them to the collector set jobs and then scripted that. Then I was able to change a few variables (and remove the schedule_uid) so that this:

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'CollectorSchedule_Every_15min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'SOME-GUID-HERE'


became this:

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_15min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO


I ran these add_schedules on the QA boxes, went through the Configure Management Data Warehouse Wizard again, and tada!

Green is good. Green is life.

For your convenience, I’ve scripted out the four schedules I needed to get it going below. These are not from the most current version of SQL Server, and are not all of the schedules included out of the box, but these let me get MDW running so I could add my own custom schedules for the remainder.

The moral of this story: Don’t remove MDW schedules unless you really mean it, for all time, forever and ever, pinky swear.

The four ‘core’ schedules I needed, for your convenience:

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_6h',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=6,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_30min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'CollectorSchedule_Every_15min',
@enabled=0,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

EXEC msdb.dbo.sp_add_schedule @schedule_name=N'RunAsSQLAgentServiceStartSchedule',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080709,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
GO

If one of these schedules gets created twice you can look up the job id or use SSMS to ‘Pick’ the schedule for the duplicated schedule (the id is in the first column). And run this to remove it:

--EXEC msdb.dbo.sp_delete_schedule @schedule_id = 9999

-Charlton Julius