October 3, 2009 at 12:38 pm
Comments posted to this topic are about the item DBAs and the Fear of Maintenance Plans
October 3, 2009 at 9:22 pm
Shoot... I don't fear the maintenance plan wizards. On the contrary... I love them. They help keep me employed by fixing large systems where they've been used unwisely. At my last job, I made a 44 hour job drop to under 24 hours just by undoing the damage caused by an improperly used maintenance plan wizard and a wanna-be-DBA that set all the DB's to autoshrink... including TempDB.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2009 at 12:56 am
I'll admit that I haven't looked at the maintenance plan capability in 2005 or 2008 because we just started using those platforms at work. I have seen them in use, and for shops that don't have a reasonably-skilled DBA, they're better than nothing. I think my biggest gripe is if you have them running DBCCs, you don't get reasonable output. I run all of my DBCCs every nightly via OSQL called by a scheduled job, and the full result file is parsed through FIND so that I see only the ERROR lines. If I see non-zero numbers, I can go back to the full output and find the offender and fix it. Otherwise, I glance at maybe 50 lines just to makes sure they're all zero numbers.
I started using this technique back in the v4 days and have no plans on replacing it with maintenance plans.
What I would like to see in maintenance plans is the ability to back up every database NOT on a list so that if a developer or another manager adds one, it'll get backed up. I'm sure there's a script for doing this, I haven't had time to find one and implement it.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 4, 2009 at 9:47 am
The thing I like about the maintenance plans is the fact that I get all of the history for the sub-plans without having to implement anything myself.
A lot of my maintenance plans end up being nothing more than Execute SQL Tasks. For example, I use this technigue to perform either a full integrity check (weekly) - or a physical only no index check daily. Don't have time every day to perform a full check - but this way I get something.
The biggest problem with maintenance plans (especially the wizards), is the fact that those using the Wizards have no idea what the tasks do and assume that performing them all is the best thing to do.
I don't think it is the maintenance plans that are causing the problems - it is the people using them without understanding what maintenance needs to be done that is the problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 4, 2009 at 2:47 pm
Personally I don't like to use them. I prefer to have a much more controlled and granular approach i.e. using a combination of T-SQL, VBScript, Powershell and CLR (.NET). You can either use these as separate steps within the job or create a SSIS package in BIDS to utilise similar components i.e. .NET Script Tasks etc.
In the past I have had the odd issue here and there where Maintenance Plans have failed for obscure reasons (usually resolved by Service Packs). So it would be fair to say, I lost faith in them.
I find that I have far more control and flexibility over process flow and error handling when using the above approach. Especially when it comes to handling backup errors. However, it does make it more difficult to understand when someone reviews your work. Therefore, I will use Maintenance Plans when the requirements need to be understood by the everyday Systems Administrator that has limited scripting or code knowledge. Complicated SSIS packages can be a nightmare to decipher at times 😀
October 5, 2009 at 7:34 am
I've used maintenance plans as a stopgap. Get something in place that backs up databases on regular basis and a few other tasks, while building the necessary scripts and data to set up something more intelligent.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 5, 2009 at 8:45 am
Maintenance plans are used by those that don't have the coding skill to create backups, defrags, and other functionality. I am weaker on creating code for replication and log shipping so I use them only for that. There will likely be a day when I won't use them for those functions either.
On every project I have been on as a consultant where I found maintenance plans in use had un-recoverable databases, massive collections of log backups but no full backups, routines that drop indexes and lockup that database in the middle of business day, etc...
I HATE maintenance plans because they are too easy to use by the profoundly ignorant. They are similar to giving a small child a fully loaded and cocked semi-automatic handgun and leaving the room.
Never use them if they can be avoided. They are too harmful because they claim to help the business and end up betraying that trust when the database is unrecoverable.
October 14, 2009 at 2:01 pm
Wayne,
Check out Tara Kizer's stored proc here. It's proven quite useful for me. Backing up User databases backs up all non-system (and non-primary log-shipping) databases.
Dustin Mueller
@sqlcheesecake
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply