SQL Maintenance Plans
-
I think the fact that it's canned and predictable. Walk into any company in the world, see maintenance plans, you know what's going on. No plans, then you have to sort through jobs, figure out how the previous person did it. Don't know that it's hard or even takes much time, just more time than checking an existing plan. I think the plans really come into their own with companies that don't have a dedicated DBA - they just run the wizard, it's good enough.
Andy
-
Is it possible to convert the maintenance plan and the jobs created by this wizard to a SQL script that adds the same maintenance plan and jobs to an arbitrary database? Any tips would be appreciated. Thanks.
-
YChen, take a look at sp_add_maintenance_plan in SQL Server BOL. sqlmaint only executes the plan. I'm also figuring out how to create the plan by hand or get the wizard's output and turn it into Transact-SQL.
-
I have the same problem when I schedule a transaction log ( SQLSTATE 4200, ERROR 8198) I belong to DBA group using window NT authentication
-
Good article!
I use maintenance plans whenever possible. On some databases a maintenance plan fails during a reindex job (caused by a calculated field in a table). The real cause is an incorrect connection setting, which you can't control in a maintenance plan. I definitely think MS should work on the ability to set the connecion parameters of SQLMaint.
JP
-
I use the Maint. Plans for our Productio & non-production servers. With the amount of servers I support at any given time (30+) it just makes things easier. Yes, the "Plans" have limitations but for these cases I just work around them using scripts to suit the purpose. One thing I have to say is not having used log shipping in the past it was fairly easy to setup and run using the Plans. Overall I'd say Maint. Plans are a value added "tool"!
-
I personally would like to be able to modify the generic (and sometimes cryptic) return messages I receive from these Maintenance Plans. A little plain more detail on exactly what was performed, how much space was recovered for the operating system and other useful diagnostic info would save me time having to verify that everything I thought was going to happen did indeed happen.
-
Maint plans are a basic toolbox set of scripts, as far as I can see.
They'll give you a basic safety net if you've never administered a database server before, or if you're just setting up, and haven't had time to develop a good strategy yourself yet.
They're a good value add, but their exclusive use can lead to inefficient backups strategies, or a false sense of security.
While I was migrating from other systems (Oracle/PostgreSQL/Mysql on UNIX) to the Microsoft SQL Servers on Windows at the current job, they made a great safety net.
Now, however, I'm starting to slowly replace all the default jobs with specifically created scripts, to ensure I know exactly what's going on, and that things are done just the way I want.
One thing I think would be a great boon on the Job scheduler though, is the ability to have a total job flag.
This would allow a multi step job to have certain stages being able to fail, but flag the job as 'suspect' once it completes.
For example, in a 5 stage job, stage 3 is doing some basic housekeeping, but something caused it to fail. This condition isn't fatal to anything, just an annoyance that may want to be looked at.
The ability to set a flag saying "This job is suspect" will give the admin the ability to pick up that there's something mildly broken (instead of very broken), while allowing later, vital stages to be performed.
Most of that, I get around by using scripting, and on periphery sequence (inside the script) fails, carrying on, but causing the script to raise an error condition on exit anyway.
-
I use the maintenance plan only for rebuilding indexes and check databases. Is simpler and it works.
The only complain that I have is that adding a step for alerting using xp_smtp_sendmail is messing up the wizard and I can not review the options in the maintenance plan.
Ionel
-
I believe one of the most anoying feaures is that it does not give you the ability to SCRIPT OUT the plan. It will be really portable and easier to deal with if you could script out the plan.
Just my $0.02
* Noel -
I disagree. I worked in an environment with well over 125 SQL Servers and we used a standard maintenance plan script, like the one in this article, for all but one server. That server had a big database that we couldn't back up to the standard location with the other smaller databases so it had a special plan. We had most of the servers locked down well enough so that we didn't get suprise database additions. We combined this with a home-grown monitoring tool that would check the status of the backups and gather some other database and server information every day and were able to build a backup failure report and correct any problems (such as truncate log on checkpoint settings) first thing every morning.
Sure, the maintenance plan tool could use some additions but I have found it to be extremely useful in an an Enterprise environment as well as small-scale installations. I wonder if there are any cool changes in sqlmaint with SQL 2005?
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
We use maintenance plans. I like them and hate them. I use them almost on all servers to run optimization and Integrity checks. Use on some other servers for log backups. They do work most of the times, how ever As most of us know, it doesn't tell you why it failed. "sqlmaint.exe failed."- pretty useful!!! for ex. If one database is in simple recovery mode and if it's in your list of log backup, maintenance plan will fail. You can check that from the maintenance plan history, but I would like to see this in job history. Also many many times, it just can't delete old backups, I have to do it manually. I would like it to be more informative when it fails.
-HIren
-
I use the maint plans. They work reliably, and have all the options I care about. I supplement them with a couple of other jobs, such as log shipping and truncating the transaction log after the weekly maintenence.
The one thing I'd like is if it wouldn't complain about steps being added to the jobs directly. I would like to tack-on extra steps to the plan, to do those actions described above.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
HIren,
You may have missed this in an ealier post but I will reiterate (and get my extra chance to win a prize
)If you right-click on the Database Maintenance Plans icon in the Enterprise Manager tree view and select Maintenance Plan History you will get a very detailed look at what your maintenance plans are doing. If this takes a real long time to load you may want to consider limiting the number of rows of history that are stored for one or more of your plans. If there is nothing in the history then you did not turn on the Write history to the table msdb.dbo.sysdbmaintplan_history. Both of these options are on the Reporting tab in the Database Maintenance Plan properties dialog.
Hope that helps!
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
I use them as they do 90% of what I want.
I wish they were a bit more robust and/or had the facility to allow a stepped action if a particular part fails.
For example, if recovery mode is set to simple on a particular database and the maintenance plan attempts a transaction log backup on a series of databases I don't want the whole plan to fail because of one database.
It would be great if there was a tool to sequence the maintenance plans automatically to make sure that they can be run end to end rather than in parallel. If I allow 1 hour per database then if one database takes 10 minutes I would like the next plan to kick off as soon as the last one finished. I know I can do this is script but it would be great if the tool did it for me.
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply