June 19, 2009 at 9:35 am
I'm a wizard man myself, as I've learned, I then take apart the wizard'd scripts and modify them, that way I get the best of both worlds, and I don't have to start from scratch and worry if I forgot something.
BTW Steve - typo on the first line:
I saw a post recently asking about whether or ^NOT^ the general community
June 19, 2009 at 10:01 am
I prefer the maintenance plans. Not wizard driven, I drag and drop the parts I need.
Why? Because awesomely well scripted tasks still run into reporting issues when they fail on the nth database. Job history doesn't show what went wrong, which means an entirely even more complicated reporting system written into this script.
Or
I can setup a maintenane plan that reports on job completion to me. If it fails I can go into SSMS and right click on the maintenance plan and look at job history for the failed databases which get listed individually in the maintenance plan history. Then I can go in there and fix whatever issue caused the backups to fail (generally somebody putting a new database on one of my instances and not taking a full backup right out). When you've got 1000+ databases per instance having the ability to track down which database caused a problem is invaluable.
June 19, 2009 at 10:55 am
Charles Kincaid (6/19/2009)
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.
I knew it!!! 😛
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 19, 2009 at 11:07 am
I find that the only the checkdb maintenance plan is of any use and the other plans lack the necessary functionality. I use SQL stored procedure in the master database that have been marked as system procedures, which format the options for the xp_sqlmaint procedure.
Take and example of a single SQL Server with three user databases, one of 200Gb, 400Gb and 600Gb. There are tape backups (or SAN copies ) that complete by noon each day.
All three databases have a selective index rebuild followed by a full backup performed on different days of the week with differential backups on the other days. Since maintenance plans would not delete the full backup until after the next full backup succeeded, that would require 1.2 Tb of disk space, so instead, just before any backup runs, all *.bak files are deleted from disk.
Maintenance plans only allow log backups on a time scheduled basis. Instead, a log backup should be run whenever the log space used percent exceeds a threshold. Alerts cannot be used, because for small log files, a 39% used is reported even when the log is empty. Another requirement is that to reduce the exposure window, when the amount of time since the last log backup is exceeded, a log backup should be run.
For index rebuilds, there are two significant pieces of functionality that are lacking:
1. When in full recovery mode, the index rebuild will write to the transaction log faster than the log can be backed up causing the log to run out of space. Using maintenance plans, the transaction log needs to be the same size as the database or the maintenance plan will fail. Instead, a custom script that checks the log space percent used after after each index is rebuilt, and when a threshold is exceeded, a log backup should be performed.
2. Sometime there is a maintenance time window and you want to rebuild the most fragmented indexes until the end of the window is reached but this is not supported by maintenance plans.
Unfortunately, my client has insisted that these SQL scripts are proprietary and will not allow me to publish them.
SQL = Scarcely Qualifies as a Language
June 19, 2009 at 11:32 am
David B (6/19/2009)
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.
Nicely put.
June 19, 2009 at 12:44 pm
Dan Guzman (6/19/2009)
I'm a wizard man myself, as I've learned, I then take apart the wizard'd scripts and modify them, that way I get the best of both worlds, and I don't have to start from scratch and worry if I forgot something.
I agree. I like maintenance plans and don't think there's anything inherently simplistic about using them. But it is better to understand what the maintenance plan is doing behind the scenes to avoid becoming simplistic in trusting the plans to take care of everything.
I haven't taken apart maintenance plans yet but will now try to do so. More so than with other GUI applications such as Word or Excel, knowing the underlying scripts or procedures used is important in SQL, because you actually have the chance in most cases to use the scripts directly instead of having to rely on the wizard. One way to automate processes, for example, is to find out what the script for the wizard/GUI does.
Plus sometimes you need to refer to both the wizard and the script - as seems to happen when trying to script Profiler trace templates where the output occasionally has bugs in it.
Best,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
June 19, 2009 at 1:56 pm
Starting out a couple of years ago, I relied on maintenance plans and the GUI in general.
As I gained experience and found that the GUI did not always produce the desired or expected results, I started using for scripting.
Still, I find I like to use the wizard to set up the original maintenance plan. I then usually make modifications to have it work the way I want it to. Just like to make sure I have something in place when I set up a new DB.
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
June 19, 2009 at 2:14 pm
sknox (6/19/2009)
Okay, seriously, the personality trait I look for is interest. If my protege has got the interest to want to grow and learn, I would recommend scripting -- and I would check their work for a while. If they are doing this job for the money, and don't really want to grow, throw the wizard at them -- and then check their work for a while.
Well said. And someone else said something to the effect that you don't really know the material unless you can script it. When I became an accidental DBA I tried using the maintenance plans but they were failing. Granted, I was trying to backup the transaction log on a DB whose recovery mode was simple 😀 (not sure why the GUI allowed me to even select that).
Then I took to scripting our backups and that's when I started to learn about this aspect of DBA work. And I feel the scripting gives me more control of the process.
Ken
June 19, 2009 at 3:52 pm
The perspective about DB-Maintenance, for me as DBA, is always related to organizational (and DBA-team) point of view. I've been member of teams where db-maintenance plan is a golden rule inside of the team(which I particularly prefer); and some other organizations where noone speak about them, and everything is working fine.
Sometimes you want to schedule a maintenance over a database that it's so huge that the maintenance itself affect business processes. When your boss take you to the small room to talk about it, and ask you: But did we have any problem? Why did we run any maintenance if the performance is OK? Or it cancels a Backup process, which is vital because should run before a big ETL-process. So the boss finally tells me forget about maintenance, let all other process run OK.
I know what most of you'll say. I'm very aware (and I have done so in most of the cases) that this is a hassle that can be handle; just scheduling the db-maintenance process according to the entire server schedule of adjacents processes, so they don't cross to each other. I often try all the efforts to convince my boss of the benefits of db-maintenance, but if he keeps his point of view, and given the fact that he gave me an order, I simply drop out the maintenance idea and leave the database only with the defaults AUTO-UPDATE & AUTO-CREATE statistics. After all, we have a lot to check, optimize, verify, scheduling, tune, anyway, and that could be one less. 😀
Regarding the EMBEDDED/SCRIPT maintenance, perspective: my little experience tells me that in the end, the EMBEDDED(Wizard-GUI) is the fastest way to implement; and SCRIPT could requiere a lot of time to test (check syntax, switches, etc.), and remember if there is a thing a DBA don't have is 'time'.
So to me, using BOTH is the most complete; cause usually the EMBEDDED has not all the customization that you as DBA want, even when we have to admit that SQL2K5 and above are closing that gap very well. 😉
June 20, 2009 at 12:26 am
David B (6/19/2009)
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.
Yes, I agree that should you know your way around scripts that you should rather use them but stay away if you don't. I used the maintenance plans when I was thrown in at the deep side but plan to replace then all with scripts as soon as I am confident with them. Might I just mention that a client of mine (rather small company) could not afford to by a server as well as a SQL Server so they opted for a sever with SQL Express. Now I can't do maintenance plans to setup automated backups and using scripts in .bat files with Windows Scheduler and it works wonderfully. So, if you can't use or don't have maintenance plans to use then scripts is the way to go! Be carefull though!!!!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 20, 2009 at 12:35 am
mbricker (6/19/2009)
Starting out a couple of years ago, I relied on maintenance plans and the GUI in general.As I gained experience and found that the GUI did not always produce the desired or expected results, I started using for scripting.
Still, I find I like to use the wizard to set up the original maintenance plan. I then usually make modifications to have it work the way I want it to. Just like to make sure I have something in place when I set up a new DB.
Whe I was thrown into become a DBA I started learning to use the maintenance plans. When i got that right and I got my first e-mail from DBMail to say the jobs was successful I thought I was the man until I read something on SSC and I scrounged back into my little corner and said to myself: Self, you have a lot to learn. When I scripted my first job for backup and for setting up a recovery plan, I told myself: Self, now you deserve a pat on the back! But that als did not last long because SQL Server has the ability to humble a person.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 20, 2009 at 3:51 pm
Manie I'm having the same "good time" with Express. Then I've had my troubles with the Windows Task Scheduler. Ran into one of those issues that Redmond has classified "will not fix". :crying: So I wrote a light weight replacement for the Windows Scheduler. All smiles now.
ATBCharles Kincaid
June 20, 2009 at 10:05 pm
Heh... like everything else, "It Depends". 😛 I'll do it 3 different ways depending on the situation and the database... use the Wizard to setup a plan, use the Wizard and then tweek the DB with a script, or script only.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2009 at 8:22 am
Charles Kincaid (6/20/2009)
Manie I'm having the same "good time" with Express. Then I've had my troubles with the Windows Task Scheduler. Ran into one of those issues that Redmond has classified "will not fix". :crying: So I wrote a light weight replacement for the Windows Scheduler. All smiles now.
Would you care to share that with us? Something like that could always come in handy especially since I am not so crafty with scripting in Windows.:cool:
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 21, 2009 at 9:23 am
I've told the boss that we could sell our little scheduler for a few bucks a copy. The only problem I've every had is security context issues. It launchs all of it's processes with the same credentials under which the service runs.
Here is a free tip that will help a bunch of folks get started with scripting.
(1) Come up with a SQL statement that does your job.
(2) Run it through the command line utility (OSQL for 2000, SQLCMD for 2005 and 2008)
(3) Write a Windows Command file that contains (2)
(4) Schedule that command file with your favorite scheduler
It's a way to get your feet wet and get around what Express does not have. OK there is no error checking. It does the same thing over and over without change. It's limited in what you can do.
Then you could do things like get some good developemnt tools and write software that writes software. Huh? I can write programs that connect to my SQL server, figure out what SQL statements need to be run, write them out to a file, and have my command file go execute them.
Right now Microsoft gives away VB Express. It's more powerful than I thought and runs on my XP Home. Then there is Power Shell.
ATBCharles Kincaid
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply