Attack of the Killer Maintenance Plan

In tracking down some system resource use issues, it led me back to some maintenance plans that I knew needed re-writing (ideally replacing) but I never expected them to end up the horror movie they revealed. {slurp, thunk}

It's a sunny day. Sure, it's Monday, but you get those now and then. My role at the current company is inherited. They had another DBA previously who did not have prior DBA experience. It shows - maintenance plans everywhere. Really, that's not the end of the world, many companies who do not have a full time DBA use them, smaller shops use them as well and those who don't have other known options implement them too. That's okay, this is part of what my paycheck is for, right? So, while the task has taken a back burner for many months, it's time to fix them up. Especially when it appears to be a culprit in pegging my DEV server on a regular basis. Hey - look! - there's an old abandoned house at the top that hill...let's go see if the door is locked.

Turn the creaky knob - let's go in and see what's up with the plans. Oh, look-y there! Not ONE maintenance plan, but TWO. Well, at least the previous person separated out 'system' database maintenance plans from 'user' db plans. Let's see what is down that dark hallway behind the door...Maintenance plan for 'user' databases. SLAM! hey...who closed the front door? Take a deep breath, it's just wind. Sure. Now, back to this door at the end of the hall. Slowly! You never know what might be in there. Huh. This doesn't seem so scary. Rebuild Indexes, Update Statistics AAACCKKK!!! Where did this Shrink Databases come from! Quick - get the baseball bat. Hit it! Hit it HARDER! ONE MORE TIME! Whew. That was close. As we back out of the room, we catch a glimpse of Check Database Integrity. I think we'll be okay.

One more door to check though. It's a door in the kitchen. Surely this must lead to the basement? Do we *have* to look? Yes? Gulp.

What is that scratching sound? Hey wait - there's a sign on the door. It says "optimize system databases". Huh. I don't want to go in there. You go first.

Oh, my blog? I have to go first? Got it. Sure. Okay. Reaching out with a shaky hand to the handle on the door and - whoa....it swings inward? That's just not right. And the lights are already on. Stepping down one squeaky step at a time, we see Rebuild Index again. Looking a bit worse for wear but I'm sure we're safe. {gurgle} What was that? Whew...just Update Statistics again. {slurp, thunk} There was something in the shadows there. What do you mean "where?" Right. THERE. AAAAAACCCKKKK! Another Shrink Database - No! No! It cannot be! Not here! Quick, behind this door...RUN!

My, but that was close. I've never seen anything so heinous. A few more deep breaths.

Ewwww. What is *that* smell? Etched on the opposite wall of this cellar room, the word "Production" and an arrow pointing to another door can be made out. I don't want to think of what was used to write that. We have to check the Production server now. There just has to be a way out of here. Please don't make me see that basement again. {tap, tap, tap, tap} Stop it now, you're just trying to scare me. {drip, drip, tap, tap, tap} Oh dear.

Two Maintenance Plan doors again. I'm not even going to attempt the "User" plan again. The only way out is through the "System" Plan door. The door is already opened a crack and there is an eerie greenish light emanating from somewhere within. The tapping sounds are getting louder as we approach. Within inches of the door, it swings open with a BANG and out run Rebuild Index and Update Statistics as if they had been sent on a mission. We are knocked over and fall to the cold floor. Trying to regain balance from taking a hit, the light catches our eyes. It's unimaginable. The mass is pulsating, amorphous and...and..

And then, there it is in all it's GUI glory:

USE [master]
GO
DBCC SHRINKDATABASE(N'master', 10, TRUNCATEONLY)

GO

USE [model]
GO
DBCC SHRINKDATABASE(N'model', 10, TRUNCATEONLY)

GO

USE [msdb]
GO
DBCC SHRINKDATABASE(N'msdb', 10, TRUNCATEONLY)

We have reached insanity. There is no hope of return. Our meager baseball bat cannot contend with such a monster. Cthulhu has won this day.

Slurp.
Thunk.