1

SQL Server Maintenance Plan Help

I'm using SQL Server Maintenance and found a way to selectively turn on and off various features of the plan via it's associated job shown in Figure A. This can be handy for me because I can create multiple versions of the job and selectively turn parts of the plan on or off as needed--without going into the GUI interface for the maintenance plans.

However, if I should open the associated plan via the maintenance plan GUI and save it--even if absolutely no changes were made--it deletes all of my customization in the Set values tab of the job--as shown in Figure B. I ran a trace, shown in Listing C, the shows my job being replaced with vanilla values.

Question:

Does anyone know how to stop this annoying behavior that erases the values in the Set values tab? (Or alternately to add my values to the plan GUI so they get added to the SQL job when I hit save).

p.s. SQL Maintenance is definitely not my favorite. I'm a T-SQL guy at heart but the company I'm currently at wants to use SQL Maintenance Plans.

Figure A: Maintenance Plan Job with customized values

enter image description here Figure B: Maintenance Plan Job values erased by resaving the maintenance plan in the GUI

enter image description here

I ran a trace and found that the GUI was recreating a vanilla version of the job after I resaved it--deleting my original values:

Listing C: Job values get replaced/erased

EXEC msdb.dbo.sp_add_jobstep @job_id=N'a76facdb-d100-46a0-9b7a-4f0fab85178c', @step_name=N'Subplan_1', 
        @step_id=1, @subsystem=N'SSIS', 
        @command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\Nightly Maintenance" /set "\Package\Subplan_1.Disable;false"'
  • 5
    This crazy behavior is exactly why most of us don't use the maintenance plans and the clunky/glitchy GUI. I'd highly recommend switching to Ola H's solution. It's much more reliable and resilient, plus has many more options. – Kris Gruttemeyer Jun 24 '16 at 14:08
  • Indeed--I hand-crafted my own--years ago. But the new DBAs at this company are absolutely convinced that SQL Maintenance is written by Microsoft--so it has to be good. They even have management drinking the cool-aid. – Sting Jun 24 '16 at 14:12
  • I mean, they're not awful if you don't have time or are a part-time/accidental DBA. If you have a team of DBAs though, Ola's Script are the way to go. We have all of our options scripted in with it, default path, Agent Schedules, etc. 1 button and the server has its maintenance set up. You can lead a horse to water, can't help if they use it as a biday. – Kris Gruttemeyer Jun 24 '16 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.