When I started to hold the mantle of DBA I got jazzed about maintenance plans. They were easy, they made sense, and it was graphical. Three things I look for in any administration task I do. So I am thinking "awesome, I've got everything set, I'm backing up, doing maintenance on indexes and stats alike".
Yeah, no one likes maintenance plans.
Brent Ozar (web | Twitter) explains why actual DBAs don't like maintenance plans. Who knew? People who've been at this longer than I have. I have come across many an odd error due to a failed maintenance plan. The best one yet has been an error about invalid credentials when connecting to a file share where the backup is being written (which is another post to debate whether or not this is a valid/accepted thing to do). The way I used to fix it? Change from using the DNS(\\archive\SQL\backup\) name to using the IP (\\192.168.1.107\SQL\backup\) address. No, I'm not kidding. Brent (and probably others) is correct in saying that maintenance plans just do weird things.
I have decided that enough is enough. I have the self confidence now with my understanding of the way things work that I am going to take the plunge and convert my maintenance plans. I have broken down my maintenance plans into pieces. Instead of doing DBCC, rebuild/reorg indexes, update stats, and backing up all in one plan, this will be done all in one job. Each plan task will be broken out into t-sql scripts and made into steps into the main agent job.
In following postings, I will go step by step of the deconstruction of my maintenance plans.
No comments:
Post a Comment