Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBAs and the Fear of Maintenance Plans Expand / Collapse
Author
Message
Posted Saturday, October 03, 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: Wednesday, April 16, 2014 9:53 AM
Points: 569, Visits: 985
Comments posted to this topic are about the item DBAs and the Fear of Maintenance Plans
Post #797425
Posted Saturday, October 03, 2009 9:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:22 PM
Points: 36,016, Visits: 30,308
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

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

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 10:26 AM
Points: 891, Visits: 1,958
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.
Post #797507
Posted Sunday, October 04, 2009 9:47 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 4,379, Visits: 9,472
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #797558
Posted Sunday, October 04, 2009 2:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 05, 2009 7:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 10:04 AM
Points: 15,442, Visits: 9,590
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
Post #797877
Posted Monday, October 05, 2009 8:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 06, 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: Wednesday, February 05, 2014 2:37 PM
Points: 33, Visits: 593
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
Post #803076
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse