Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

DBAs and the Fear of Maintenance Plans Expand / Collapse
Posted Saturday, October 3, 2009 12:38 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Monday, November 28, 2016 10:48 AM
Points: 569, Visits: 1,145
Comments posted to this topic are about the item DBAs and the Fear of Maintenance Plans
Post #797425
Posted Saturday, October 3, 2009 9:22 PM



Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 42,081, Visits: 39,473
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #797493
Posted Sunday, October 4, 2009 12:56 AM



Group: General Forum Members
Last Login: Friday, October 14, 2016 12:07 PM
Points: 1,518, Visits: 3,694
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
Post #797507
Posted Sunday, October 4, 2009 9:47 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:06 AM
Points: 4,377, Visits: 9,710
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
Post #797558
Posted Sunday, October 4, 2009 2:47 PM


Group: General Forum Members
Last Login: Sunday, May 26, 2013 5:33 PM
Points: 10, 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

Post #797593
Posted Monday, October 5, 2009 7:34 AM



Group: General Forum Members
Last Login: Monday, August 29, 2016 1:09 PM
Points: 13,999, Visits: 9,728
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.

Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #797877
Posted Monday, October 5, 2009 8:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 6, 2010 1:30 PM
Points: 34, 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.
Post #797962
Posted Wednesday, October 14, 2009 2:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 5, 2015 10:20 AM
Points: 33, Visits: 621

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
Post #803076
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse