Every now and then, I hear an experienced DBA say, “SQL Server maintenance plans for backups, indexes, and statistics? What, are you kidding me? Nobody uses maintenance plans because they suck.”
They say maintenance plans are so inflexible because they insist on defragging or rebuilding every index, every time, regardless of how little the table has changed. Or maybe they complain about problems with scheduling, or how they saw somebody use a DBCC SHRINKDB maintenance plan once.
Well, I use maintenance plans all the time.
I even suggest ’em to my clients.
In a perfect world, every DBA would have an unlimited amount of time to get the absolute best solution to every problem. We’d spend endless time tuning T-SQL, perfecting plans, and daddying databases. We’d drive home from work in our Porsche 911s to our mansion on the hill.
Back here in the real world, we have a limited amount of time to work each day. We need to implement solutions that anybody can troubleshoot and maintain with a bare minimum of knowledge. Sure, you might be the only DBA in the shop today, but wouldn’t it be nice to get a junior person hired, and then immediately be able to pass work off to ’em? I’ve seen shops where the lead DBA is still personally managing their homegrown maintenance scripts – the DBA doesn’t trust anyone else to touch this convoluted spaghetti code.
In the real world, we usually have two, or sometimes three, problems:
- The servers need to be maintained
- We need to save DBA time
- We need to save server time
Everybody has problem #1, but often problem #3 is overrated. With careful use of maintenance plans, we can solve problems #1 and #2 easily. Could the maintenance be done faster with custom scripts? Yep – but when those scripts fail – and they will fail – we’ll make problem #2 worse, not better.
Maintenance plans aren’t perfect, but they require less troubleshooting time than your homemade scripts. I know, you think your scripts are perfectly bulletproof and would never possibly have a bug, but time after time, I do SQL Server health checks and find some amazing problems. Some highlights have included:
- A t-log backup script that failed every night at 11pm without sending a warning
- A 1mb database with 100GB of transaction logs because people thought it was in simple mode and their script skipped it by name
- Databases that had never been backed up because the script used the wrong variable name for the database
- DBCC jobs that completed successfully in under a second and nobody noticed. They were using sp_msforeachdb, which is notoriously unreliable.
Maintenance plans have a killer advantage over custom maintenance scripts because they don’t have your bugs. Let’s face it: we’re just not that good at writing code. If we were, we’d be developers, not DBAs. They do have bugs – but the bugs tend to be in bad implementation, like doing SHRINKDB, and those same problems pop up in custom maintenance scripts too. If you’re smart enough to write a maintenance script, then you’re smart enough to use maintenance plans correctly. (And conversely, if you’re not smart enough to use maintenance plans correctly, maybe you should ease up on those scripts, eh?)
Maintenance plans are like my iRobot. Their Scooba is a floor-cleaning robot that sprays cleaning solution on my wood floors, scrubs it (a little), and vacuums the liquid and dirt up. It doesn’t do a great job – in fact, it does a pretty crappy job, leaving a damp trail of cleaning solution behind. At first this bothered me, and I searched the web looking for ways to get it to do a perfect job and leave a dry floor behind. No dice – everybody complains about the dampness.
Thing is, J-Lo (that’s my name for her, because she’s On the Floor) does a better job of floor maintenance than I can do in my spare time. Once a week, I fill up J-Lo’s tank with cleaning solution, put in her battery, and turn her on. I watch her do her thing while I write blog posts (and she’s doing it right now – I know you want to watch, but this is a private show.) The cleaning solution evaporates, and I’m left with a cleaner apartment than I’d have had otherwise.
If you insist on using crafty scripts to do your database maintenance, use Ola Hallengren’s free scripts. They’re powerful and he even updates ’em frequently – just this week, he added support for the new preferred-replica-backup DMVs for SQL Server 2012’s AlwaysOn Availability Groups.
That’s the curse and blessing of scripts – even good scripts like Ola’s require maintenance because new versions come out from time to time. If you don’t have the time to maintain and debug scripts, use maintenance plans. Take all that time you save, and keep reading blogs to fix problems that actually bother your end users.
30 Comments. Leave new
Good article. I use maintenance plans all the time, being a part time dBA, the maintenance plans make my life easier. Don’t like the emails I get when something fails, but at least I know there is a problem I need to research.
i use them on all my servers – but i don’t just use the default tasks SQL provides – i simply use maintenance plans as a handy container to organize the execution of SQL Agent jobs – such as Ola’s scripts create.
I agree wholeheartedly! It is the same reason many customers that I work with would rather buy a new server than make code more efficient. It is cheaper to allow some processes to be inefficient than taking the manpower to really tweak and customize it. Maint plans are the right solution for some situations, and they are the wrong one for others.
And on the Scooba note…. I have one too and they are nice although expect the grout in any tile floors to end up brown. I run one every day in my 3 season room which should really be named the “dogs got muddy so they are in here until they are dry” room. But just like maint plans, Scooba cleaning is better than no cleaning!
SQLGardner – ooo, yep, I can see how it’d leave a channel of dirty water in the grout!
It beats nothing I suppose. But maintenance plans won’t help you when you leave Adventure Works my friend.
most of the time, people just diss maintenance plans bc they think it is the ‘elite SQL DBA’ thing to do.
Michael – totally agreed! I do see that elitist attitude, and it’s something I try to break down to improve the DBA community.
I think maintenance plans are better than nothing, but recommend then above things like the Ola’s solution is something I couldn’t agree with.
And it’s not about elitist attitude, it’s about which option is better. Of course it isn’t perfect (require maintenance), but even in the worst scenario (something is break and Ola couldn’t fix it) you could improve it or change what is broken or adapt it in much less time than MS could do with the Maintenance Plans.
But it’s only my opinion…
CSM – When you say “something is break and Ola couldn’t fix it,” it’s not a matter of Ola fixing it. It’s a matter of the company’s DBA handling it. Ola isn’t on call for companies. That’s why maintenance plans often work better for companies without DBAs – they’re sealed appliances that Microsoft has already tested.
Mmmm… maybe the minor problem for a company without DBA could be the Maintenance Plans 😉
Seriously: if the company has a (real) DBA, I think the SSMS’s Maintenance Plans couldn’t be the first option.
CSM – many companies don’t have DBAs, or don’t have enough DBAs. Three of my clients here in Chicago have been trying to hire a DBA for over a year.
It has been the first option for me for over a decade and I’ve never had issues. In fact I’ve probably saved myself quite a bit of time sticking with the standard plans.
If someone wants to monkey with them, to each their own. They wouldn’t do it on my companies time unless an obvious problem existed and that problem was proven to be solved with some custom scripting (backed by statistical data), however.
I love it when people put the word “real” in front of “DBA”. That is rather comical. So if I use maintenance plans, am I no longer “real”? 😉
Can you please tell me, I am going to take a backup of database and it’s shows error that transaction log file is full, is this case what I will do to empty transaction log file.
It sounds like you might have the full recovery model enabled and aren’t doing log backups. Check out this page for more information on why that happens and how to set up log backups: https://www.brentozar.com/blitz/full-recovery-mode-without-log-backups/
Microsoft has always been oddly dismissive of data professionals with the ‘anyone can do it’ sort of nonsense. Any can do some of it. Wizards like Maintenance Plan can help for awhile. Once there’s real trouble they either call for support or talk to a real DBA.
Nice blog post. I agree that for many shops Maintenance Plans is a good solution. One advantage that I feel that scripts has and that makes it a better solution in larger environments, is that they are easier to install and standardize.
When I had only three instances, maintenance plans were ok, and I used them to do all the typical tasks. My farm has grown and today I (alone) have twenty-seven production instances, eighteen with failover (thirteen using database mirroring, five using log shipping), and over eleven hundred databases in production. Somewhere between three and twenty-seven instances, maintenance plans became far less useful due to the paucity of options.
Maybe there’s a rough, blurry line past which we could agree that non-MP solutions make more sense. For me, it was when I simply had not enough room to run a rebuild of every index in my 1.5 TB farm. Now I have a rebuild/reorg script served from MSX to every instance; if I need to make a change, I do it in one place.
Thanks Brent. I attended your Virtualization and Storage Basics for DBAs @ Vegas last month. I use maintenance plans for my regular maintenance activities..It helps me a lot.
Siva – cool, glad I could help!
There was a time a couple of months back at my work, during which I debated on using maintenance plans for health checks and sql server backups, during which I heard exactly what is written in your opening line of this blogpost. It is a big re-assurance for my views on this topic after reading your blog. Thank you thank you thank you!
My back and forth with maintenance plans vs. jobs is because of two things: Automated Deployment and Server Time.
While maintenance plans do make maintenance more consistent, the DBA time it takes to manually setup them up is killing me. Imagine setting up maintenance on 100 VM’s. Manually creating maintenance plans per box is just untenable. I prefer to run those Ola Hallengren scripts to set up maintenance in one quick stroke of the F5 button. This comes with some added benefits like not updating statistics that don’t need it, and only performing index maintenance where necessary.
The generic rebuild all indexes maintenance plan task can definitely be troublesome.
John – absolutely, I’d agree that if you’ve got 100 SQL Server instances, you should be using scripts. It’s much more effective.
Good post Brent! I must admit to being a “Maintenance plans SUCK” snob. But I’m a hypocrite and willing to admit it. I do use them. Small single purpose boxes that only have a few databases on them. I don’t want to install the expensive TDP for SQL Agent for my Tivoli backup solution. I can reliably set them up, they do the back ups, they rebuild the indexes, they do the DBCC check, it’s like Ron Popiel. Just set it and forget it! Looking at the scripts like Ola’s does give you a better understanding of what it’s doing under the hood, and give you insight into using them correctly. It does all 3 objectives, leaving me time to tweak and tune our large environments, without having to update the scripts on these 12 stand alone servers I have hanging around that we can’t get rid of, can’t consolidate as they mostly run 3rd party tools that had horrible installation requirements.
Well this has been interesting……….
I will play and be the middle man. I use both custom scripts and Maintenance plans Each is useful for the situation at hand. Neither one is perfect for all uses and niether one “sucks”. I a bit of a pragmatist and opt for the concept of using the best tool for the job at hand. Simple things like database backups of the system databses and other relatively small ( 1TB) spanning many filegroups or partitions, there are better ways than Maintenance plans.
So the Good-Bad dialog needs to reamin within the context of the job at hand instead of the global declarations. I have a narrow window per day to backup TB’s of data, no way a Maintenance plan can do this. That does not mean they can not be helpful in other circumstances.
Respectfully,
David
Brent,
You should continue to host similar discussions. You could start with “Crayola: Chartruse vs. Spring Green — is there really a difference?”. Then you could move onto more beefy topics like “C# vs. VB.Net: Who’s sorry now?” to see how it goes.
Yours always
Rowland
Hey Brent,
Second time poster, long time reader, most of all thanks for your contributions to the community, I hope you know how much they are appreciated.
I am looking at writing up T-SQL scripts (or using Ola’s scripts) for backups rather than relying on maintenance plans for all servers within the business – mainly to give me that granular edge. I was thinking of using Multiserver Administration and handling this all from my Master Database Server “Boris” (The Dictator) (haha – yeah good one Glenn).
Anyway, this way all backups can be administered from one spot (and backup scripts and scheduling can be modified from the 1 place). Yes some databases have different requirements from others but generally speaking the backup plan is the same for 95% of servers within the business.
Good idea??? Maybe, maybe not, but I’m just interested to hear from others in the community as to the pro’s and con’s of this approach. 🙂
Thanks again,
G
Does Standard Maintainence plan is a good option for a very busy server with 300+ DB’s with total size more than 1 TB+.
Can we do Smart Indexing Maintainence in standard plan.
Please share your thoughts. I am more inclined to use Custom Tsql code, Jobs for my maintainence as that offers flexibility and also I am able to apply the same code to 100+ servers in my environment.
I think we have to create maintainence plan seperately for all envionments. Please share your thoughts.
Amit – go ahead and read the post. I’ve got the answer cleverly hidden in the last couple of paragraphs. Enjoy!
I got the answer in one of your comments. Thanks !
>>>>>>>>>>>>>>>>>>>>>
Brent Ozar
April 18, 2012 4:31 pm
John – absolutely, I’d agree that if you’ve got 100 SQL Server instances, you should be using scripts. It’s much more effective.
Isn’t reading awesome? 😉 Amazing what you can pick up that way.