Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Maintenance Plans


SQL Maintenance Plans

Author
Message
Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8166 Visits: 2711
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
kpsqlcent
kpsqlcent
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

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



Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8166 Visits: 2711
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
Lee Dise
Lee Dise
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 21
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



Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8166 Visits: 2711
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
Rayven
Rayven
SSChasing Mays
SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)SSChasing Mays (627 reputation)

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

rmalayter
rmalayter
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.



julliff
julliff
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
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!



Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8166 Visits: 2711
It will do complete or log, not differential.


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
VegaMachine
VegaMachine
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1060 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
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