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 12345»»»

SQL Maintenance Plans Expand / Collapse
Author
Message
Posted Monday, April 8, 2002 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 12:08 PM
Points: 6,790, Visits: 1,904
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/sqlmaintenanceplans.asp>http://www.sqlservercentral.com/columnists/awarren/sqlmaintenanceplans.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #3482
Posted Thursday, April 11, 2002 5:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 4:07 AM
Points: 75, Visits: 394
A good article. My wish list for maintenance plans is that you could add steps to the generated jobs without affecting the plan definition. I know Microsoft say don't alter the generated jobs but it is so convenenient. We altered our maintplan backup jobs so that when a backup is taken we run a procedure to generate code to recover all databases (for disaster recovery puroposes) this works fine but does affect you ability to regen the maintenance plans. You can get around it (we have a scripts which can strip all of our added steps and one which will re-add them) but it's a pain.




Post #31368
Posted Thursday, April 11, 2002 5:50 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 12:08 PM
Points: 6,790, Visits: 1,904
Ah, good idea. Rather than hard coding a few extra features, make it extensible? I could go for that!


Andy


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #31369
Posted Thursday, April 11, 2002 7:12 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:16 PM
Points: 284, Visits: 18
I use maintenance plans. But my pet peeve is any software that, when something breaks, provides little in the way of meaningful feedback about the cause. (As an aside, my experience with web developers as a species is their absolute refusal to include conditional displays of the commands they're sending to the database -- too much of my time is spent looking at their code and trying to intuit the actual command being sent in.)

DTS is rife with this flaw: Nine out of ten failures yield "Error 22029: The step failed." Yeah, like, "No shinola, Sherlock!" How hard would it have been for the developers to have found a way to inform the user (me, in this case) the source of the actual problem? It *knows* what the problem is, it just won't tell me.

The benefits of maintenance plans, in my mind, outweigh the irritations. But it would be better if the jobs they spawned would specify any problems they encountered. As for DTS, I don't bother with making my own packages. If something breaks, I like to know where to start fixing things.

Black boxes are fine for things that always work, which is precisely why I despise wizards: They don't always work. Sometimes, they don't even "most often" work. Just last month, I wandered into a disaster during what should have been a routine upgrade from SQL Server 7 to 2000. Point --> click, what could be easier? Only problem is, the wizard broke and henceforth resisted all of my efforts to cajole it to function correctly. (I never did discover why it broke; if the wizard knew, it wasn't sharing that knowledge with me.) At least, with a checklist and a command line, as inconvenient and error-prone as that is, you are the pilot. When a wizard is doing the work, you are the passenger, and when the wizard begins steering for the side of a mountain at full speed, all you can do is scream.





Edited by - Lee Dise on 04/11/2002 07:13:24 AM

Edited by - Lee Dise on 04/11/2002 07:42:42 AM



Post #31370
Posted Thursday, April 11, 2002 9:09 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 12:08 PM
Points: 6,790, Visits: 1,904
Good comments. The only thing I can say about DTS is that at least you can save the code as VB and step through, essentially using it as a super code generator. That usually gets you pretty close the error. I'd like to see all wizards have a code generation option - my favorite example is Excel macro's - record a sequence and it generates vanilla VBA that you can run, modify, etc.


Andy


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #31371
Posted Thursday, April 11, 2002 9:49 AM


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: General Forum Members
Last Login: Wednesday, October 22, 2014 7:12 AM
Points: 585, Visits: 385
An excellent article. As a SQL DBA that was thrown in at the deep end, it provided a way to swim, and even now that i know my way round SQL like a roadmap, I still find it far more useful.
I have to agree that better feedback would be great, some of the errors are a little esoteric, even if you have got half a mind what has just kicked up the daisies.
Finally, like the DTS, a way of saving VB code or something readable would be great, at least this would help in the documentation process.




---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
Post #31372
Posted Thursday, April 11, 2002 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 2, 2008 2:50 PM
Points: 1, Visits: 3
SQL Server's Maintenance plan wizard is unusuable in a serious environment because it doesn't handle new databases or transaction log backups nicely.

On most production SQL servers, some databases have truncate log on checkpoint, turned on, others have them turned off. A complete backup procedure needs back up transaction logs hourly or more frequently. So it needs to look at every database, identify the log settings, and decide whether or not to back up the log.

For nightly backups, the procedure needs to backup the logs first, then do a full database backup.

And of course, the backup procedure needs to handle rotation of backup media (file or tape), ensuring that N days of backups are on-hand, but everything gets copied to tape automatically.

And it needs to log errors and send alerts when anything in the whole preocess goes amiss.

Nedless to say, I write my own scripts.



Post #31373
Posted Thursday, April 11, 2002 10:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 5, 2003 12:00 AM
Points: 78, Visits: 1
Good article, as I am not a production DBA, and find the plethora of tools really confusing.

Does the wizard only do complete backups? Should I use this for a complete backup, but then create my own scheduled job for differential? Sorry to be so dim!









Post #31374
Posted Thursday, April 11, 2002 10:50 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 12:08 PM
Points: 6,790, Visits: 1,904
It will do complete or log, not differential.


Andy


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #31375
Posted Thursday, April 11, 2002 1:52 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 15, 2010 1:20 PM
Points: 1,060, Visits: 4
I use the maintenance plans for simple things like backing up the logs every half hour, and full backups every 12 hours. It works great. If it fails, it calls my cell, and emails me. Last Sunday night it SQL Server called me and alerted me to a problem, I went in fix it and there was never a data problem and all users were completly unaware of anything going wrong. It worked like a charm!





- Vega
Post #31376
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse