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

Going Native Expand / Collapse
Posted Thursday, June 18, 2009 7:12 PM



Group: Administrators
Last Login: Today @ 2:36 PM
Points: 33,052, Visits: 17,302
Comments posted to this topic are about the item Going Native

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #738009
Posted Friday, June 19, 2009 12:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 8, 2013 5:39 PM
Points: 263, Visits: 862
I'm a fan of the maintenance plans. It offers an easy way to implement a basic maintenance regime, which is useful for people who aren't full-time DBA's. Sure, you can get into trouble using the maintenance plans if you don't know what you are doing. But you can also get into trouble by NOT doing maintenance at all! Plus, it gives the new DBA an idea of the types of maintenance they can/should do. For more complex maintenance, the plans don't really offer enough flexibility but I still think they have their place.

James Stover, McDBA
Post #738068
Posted Friday, June 19, 2009 12:33 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: Sunday, June 14, 2015 6:49 PM
Points: 526, Visits: 561
As someone who has had no formal training on SQL Server of any version and was dumped into the DBA world by the business, I can say that the maintenance plans are a very tasty little inclusion.

People are right in that they aren't flexible and that scripts can do more, but they "should" be able to keep your server running along fairly smoothly if you have no idea what you are doing and no time/inclination to delve into its depths (keeping scale in mind here...).

Remember, not every business can afford the services of a professional DBA (however briefly), regardless of how much of a good idea it might be.

Personally, I still don't delve into things unless I need to, it's not my forte. But I am glad that if I need to find a better way to do something then I have already stumbled across a useful community that collectively knows pretty much anything you could every want to know about any version of SQL Server.
Post #738078
Posted Friday, June 19, 2009 1:26 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: Today @ 10:30 AM
Points: 568, Visits: 1,599
I'm a full time DBA who is capable of scripting and understands many of the intricacies of SQL Server but I still like and use maintenance plans. If I need more control over a certain aspect of SQL maintenance, a script running as a job can be used on an ad-hoc basis. My philosophy is why make work for yourself?
Post #738093
Posted Friday, June 19, 2009 6:38 AM


Group: General Forum Members
Last Login: Thursday, January 14, 2016 11:34 AM
Points: 471, Visits: 875
Just because we can script it doesn't mean we should. It is very similar to those who use utilities to make changes vs scripting it. Some argue you aren't good at your job if you can't script it. I say, if someone gives me a more efficient way to do something i will.

One issue i saw recently with scripting is a customer of ours that has a team of their own dba's said they would take care of setting up their backups because she has a script that will cycle through and create the backup files the way she likes them. Problem with that is her script is complex and way overkill for something as simple as a nightly full backup where a maintenance plan or a simple backup step and verify step.

I try to choose the best path possible and i know i get stuck in my favorite ways as well. Scripting gives you control to the n'th degree but do you need it. There is a reason that we create functions and dll's for repeatable tasks. I see wizards and gui apps as no different here. Someone created a useful, repeatable engine to do the same task over and over.
Post #738243
Posted Friday, June 19, 2009 6:52 AM



Group: General Forum Members
Last Login: Saturday, November 14, 2015 5:53 AM
Points: 13,873, Visits: 9,610
I'm more than capable of "rolling my own" when it comes to maintenance scripts, but I don't bother. SQL 2k5 maintenance plans work just fine for me, and I use them.

Yeah, the scripts aren't as flexible, intelligent, etc., as what I might do myself. I haven't been in a situation yet where that actually mattered.

When/if I'm in such a situation, I'll customize the scripts. Till then, I use maintenance plans.

I don't use a wizard to build them, if such even still exists (I haven't looked). I drag-and-drop the items I want, set the sequence, and then create the schedule.

Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #738261
Posted Friday, June 19, 2009 7:13 AM


Group: General Forum Members
Last Login: Wednesday, April 8, 2015 9:50 AM
Points: 417, Visits: 1,160
I agree with what everyone else has been saying so far. I use the maintenance plans for most of my databases, except for the few that need a little something extra. Then I write my own scripts. I do think it is important though that DBA's know how the maintenance plans work and what SQL Server is doing behind the scenes. Don't just run them blindly.
Post #738278
Posted Friday, June 19, 2009 7:40 AM



Group: General Forum Members
Last Login: Yesterday @ 10:27 AM
Points: 2,231, Visits: 2,329
For someone who's an "accidental" or ocasional DBA, Maintenance plans are fine. They allow them to simply setup the basic maintenance of the database with little understanding of what these features are really doing.

For someone who's intermediate or higher though, scripting is definately the way to go. Some exaples of why follow:

- Backups
The features available here have some side effects which could be problems in larger environments, such as deleting old backup files is an all or nothing operation, where what would be preferable is that old backup files would be deleted for the databases who's current backup succeeded, and not deleted for databases who's current backup failed. The only way to avoid these side effects is to create a separate Backup Database Task and separate Maintenance Cleanup Task for each database instead of using the multi-select form, and make sure you get all of the process flow connections right, which can be alot of work. For creating easily repeatable and maintainable items scripting can be easier.

- Index Maintenance
The Reorganize and Rebuild Index Tasks allow you to select all or specific indexes, but what would be preferable is deciding to reorganize or rebuild or do nothing based on the actual fragmentation of each individual index.

- Notify Operator
A separate Notify Operator Task needs to be created in the maintenance plan for each potential point of failure. If multiple failure process flow connections are pointed to the same Task, they must all fail for the notice to be sent.

Scripting can also provide you with more advanced options in many cases for complex environments. I've also seen situations, (for unknown reasons) where maintenance plans seem to become corrupt and stop functioning properly. I've had to rebuild or replace a number of them over the years.
Post #738299
Posted Friday, June 19, 2009 8:07 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: Tuesday, February 2, 2016 5:25 PM
Points: 935, Visits: 2,314
It's like so many other things. The wizard will take you a long way. It may even save your "life" (note the quotes) a time or two. But, like other wizards, You are left at the mercy of the wizard. Your plans are only as good as the folks that wrote the wizard.

I needed to change a bunch of widths on VARCHAR columns recently. I thought "SSMS will let me make the change using the GUI to my development box and I can save the script to do the production change." Hmm. The resulting script made a new table, did an update to copy the data, dropped the old table, and then did a rename. HOLD ON! There are millions of rows in these tables and multiple production servers. This will take hours PER SERVER to run for each table. I have to have the disk space for two copies of my largest table. That update is a logged activity and the whole thing is wrapped in a transaction. Oh, my aching log space! ALTER TABLE ALTER COLUMN to the rescue. Takes around a second per table and does not touch existing data at all. Sweet.

As to maintenance plans: Sure that wizard can automate your backups and even have the file named for the date and time. What happens when you run out of disk space? You know that you will. How does it alert when if fails? You are watching your backups, right? Even the ones your custom scripts pump out. Especially those!

Take another lesson from central heating. It was cold in the house so my other half turned up the thermostat. Still cold. Cranked it up again. And again. Still again. Finally I hear this voice. "How's come I can't turn this up further?" What the ...? Sure enough it was against the stop. The pilot light was out and the furnace was not coming on at all.

Our congregation has a computer controlled heating and cooling system with micro-zones and a double fail safe controller. Very effective and very efficient. Still we were getting complaints that some of the rooms were too hot while the one across the hall was too cold. The sensors showed that they were within one half a degree F of each other. We went to the DIY store an bought a bunch of cheap thermostats. One in each room and several in the large gathering rooms. They are not hooked to anything at all, but no more complaints. (And you thought that this weird stuff only happens to Phil Factor.)

So would I recommend the wizard to an intermediate DBA? Sure, but with the "Dutch Uncle" advice that you watch what is does cuz' it's still your private parts on the block when things go wrong.


Charles Kincaid

Post #738334
Posted Friday, June 19, 2009 8:19 AM


Group: General Forum Members
Last Login: Wednesday, April 8, 2015 9:50 AM
Points: 417, Visits: 1,160
Just because you use a wizzard or maintenance plan to run jobs does not mean you should stop monitoring your systems. Even if you run your own scripts you should still monitor them. Running out of space can happen no matter which option you choose. Notifications can be set up either way as well. Problems occurr no matter how a job is set up. I don't think this discussion is about how jobs can fail, I think it is about whether we think maintenance plans can be useful to use and if they provide the functionality that we need.
Post #738347
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse