SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBAs and the Fear of Maintenance Plans


DBAs and the Fear of Maintenance Plans

Author
Message
Tony Davis
Tony Davis
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: Administrators
Points: 1399 Visits: 1162
Comments posted to this topic are about the item DBAs and the Fear of Maintenance Plans
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212595 Visits: 41977
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Wayne West
Wayne West
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5700 Visits: 3702
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.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18887 Visits: 10042
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Scott Taylor-159148
Scott Taylor-159148
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 138
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 :-D
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57013 Visits: 9730
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
don_goodman
don_goodman
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 82
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.
Dustin_Mueller
Dustin_Mueller
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 621
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search