January 5, 2016 at 11:04 pm
I was told that maintenance plans don't work or wont work well with sql server 2008 and they always failing that's why is better to use sql scripts or agent to run scripts how valid is this statement ?
January 6, 2016 at 2:05 am
how valid is this statement ?
Totally invalid
_________________________________________________________________
"The problem with internet quotes is that you cant always depend on their accuracy" -Abraham Lincoln, 1864
January 6, 2016 at 2:41 am
mark.williams 37494 (1/6/2016)
how valid is this statement ?
Totally invalid
But that does not mean that maintenance jobs are great. They are not. They allow you to do some pretty nasty stuff just by ticking a checkmark-box (like eg regularly shrinking databases - one of the worst things you can in SQL Server maintenance, yet presented as an option without any warnings in the maintenance job wizards). And I do not really like the way they present their results (might be personal).
Personally, I am a huge fan of Ola Hallengren's maintenance scripts. They are completely free, and they set up Agent jobs to do all the maintenance you need, with lots of options to tweak their working to your environment's needs. See https://ola.hallengren.com/[/url].
January 6, 2016 at 2:44 am
Hugo Kornelis (1/6/2016)
mark.williams 37494 (1/6/2016)
how valid is this statement ?
Totally invalid
But that does not mean that maintenance jobs are great. They are not. They allow you to do some pretty nasty stuff just by ticking a checkmark-box (like eg regularly shrinking databases - one of the worst things you can in SQL Server maintenance, yet presented as an option without any warnings in the maintenance job wizards). And I do not really like the way they present their results (might be personal).
Personally, I am a huge fan of Ola Hallengren's maintenance scripts. They are completely free, and they set up Agent jobs to do all the maintenance you need, with lots of options to tweak their working to your environment's needs. See https://ola.hallengren.com/[/url].
Like anything in life, if you dont know what you are doing you can screw anything up
_________________________________________________________________
"The problem with internet quotes is that you cant always depend on their accuracy" -Abraham Lincoln, 1864
January 6, 2016 at 2:47 am
I mean if its for some thing like checking database integrit and reindexing which one is better I know you get more control with the script but I just wanted to know if that statement is valid
January 6, 2016 at 2:59 am
The statement "doesn't work or doesn't work well" is invalid.
But if you ask which is better, then my guess is that 99% of the experts will point you to Ola's solution. Trust me, it's much, much better.
January 6, 2016 at 5:44 am
Hugo Kornelis (1/6/2016)
The statement "doesn't work or doesn't work well" is invalid.But if you ask which is better, then my guess is that 99% of the experts will point you to Ola's solution. Trust me, it's much, much better.
I'll cast my vote strongly in favor of using scripts. I don't like the maintenance plan's brute-force approach to index maintenance in that it processes all of them whether they need it of not. I loathe and detest the shrink option, which causes extreme fragmentation in the mdf. The backup doesn't trap errors and just stops as soon as it encounters one. I don't know of anything specifically wrong with the integrity checks.
There isn't much control in a check box the form gives you. There are lots of options for most of the features the maintenance plans include, but you don't get to define anything. Simply check a box and you're done. SQL scripts give you finite control over what happens, which can be a good thing or a bad thing, depending on the experience level of the person writing it.
I don't use maintenance plans. I've heard that Ola's scripts work well and I've heard that Minion works well. I use my own, but writing them was time-consuming. The published ones are maintained by people who know what they're doing.
In summary, maintenance plans are certainly better than nothing, but not much better.
January 6, 2016 at 5:53 am
Ed Wagner (1/6/2016)
Hugo Kornelis (1/6/2016)
The statement "doesn't work or doesn't work well" is invalid.But if you ask which is better, then my guess is that 99% of the experts will point you to Ola's solution. Trust me, it's much, much better.
I'll cast my vote strongly in favor of using scripts. I don't like the maintenance plan's brute-force approach to index maintenance in that it processes all of them whether they need it of not. I loathe and detest the shrink option, which causes extreme fragmentation in the mdf. The backup doesn't trap errors and just stops as soon as it encounters one. I don't know of anything specifically wrong with the integrity checks.
There isn't much control in a check box the form gives you. There are lots of options for most of the features the maintenance plans include, but you don't get to define anything. Simply check a box and you're done. SQL scripts give you finite control over what happens, which can be a good thing or a bad thing, depending on the experience level of the person writing it.
I don't use maintenance plans. I've heard that Ola's scripts work well and I've heard that Minion works well. I use my own, but writing them was time-consuming. The published ones are maintained by people who know what they're doing.
In summary, maintenance plans are certainly better than nothing, but not much better.
We are not shrinking the database we are only reindexing , reorganizing indexes and checking database integrity
January 6, 2016 at 6:30 am
allamiro (1/6/2016)
Ed Wagner (1/6/2016)
Hugo Kornelis (1/6/2016)
The statement "doesn't work or doesn't work well" is invalid.But if you ask which is better, then my guess is that 99% of the experts will point you to Ola's solution. Trust me, it's much, much better.
I'll cast my vote strongly in favor of using scripts. I don't like the maintenance plan's brute-force approach to index maintenance in that it processes all of them whether they need it of not. I loathe and detest the shrink option, which causes extreme fragmentation in the mdf. The backup doesn't trap errors and just stops as soon as it encounters one. I don't know of anything specifically wrong with the integrity checks.
There isn't much control in a check box the form gives you. There are lots of options for most of the features the maintenance plans include, but you don't get to define anything. Simply check a box and you're done. SQL scripts give you finite control over what happens, which can be a good thing or a bad thing, depending on the experience level of the person writing it.
I don't use maintenance plans. I've heard that Ola's scripts work well and I've heard that Minion works well. I use my own, but writing them was time-consuming. The published ones are maintained by people who know what they're doing.
In summary, maintenance plans are certainly better than nothing, but not much better.
We are not shrinking the database we are only reindexing , reorganizing indexes and checking database integrity
Hopefully you are backing up the database as well, for which maintenance plans are fine for the majority of scenarios, but scripts better in more complex cases, same goes for integrity checks. Never use the maintenance plans for reindexing, roll your own scripts or use the ones mentioned above or the example given in books online.
---------------------------------------------------------------------
January 6, 2016 at 6:45 am
Never use maintenance plans for reindexing -
I was wondering why ? if the option is there and we have one database why maintenance plan wont suffice for that operation ?
so are you saying that maintenance plans don't work ?
January 6, 2016 at 6:50 am
allamiro (1/6/2016)
Never use maintenance plans for reindexing -I was wondering why ? if the option is there and we have one database why maintenance plan wont suffice for that operation ?
so are you saying that maintenance plans don't work ?
it will work but will rebuild (or reorganise) all indexes whether they need it or not. The available scripts filter out only those that actually need doing. Maintenance plans can fail on specific indexes if the ansi settings are incorrect for that index.
---------------------------------------------------------------------
January 6, 2016 at 10:30 am
george sibbald (1/6/2016)
allamiro (1/6/2016)
Never use maintenance plans for reindexing -I was wondering why ? if the option is there and we have one database why maintenance plan wont suffice for that operation ?
so are you saying that maintenance plans don't work ?
it will work but will rebuild (or reorganise) all indexes whether they need it or not. The available scripts filter out only those that actually need doing. Maintenance plans can fail on specific indexes if the ansi settings are incorrect for that index.
Processing all indexes (whether they need it or not) can consume a considerable amount of resources. Also keep in mind that reorganizing an index is a fully-logged operation.
January 7, 2016 at 8:41 am
My problem with maintenance plans is the lack of reporting. I'm old school: I started writing maintenance scripts with the first version of SQL Server and I've continued the habit, up to writing Perl scripts to provide me with a daily summary of what my servers were up to. I experimented with maintenance plans for DBCCs and found that I didn't get the detailed information that I wanted to track, whereas my own T-SQL scripts gave me full DBCC output and a batch job sent me an email that contained a summary that showed me whether or not the DBCCs were clean. If the summary showed me an error, I had the full DBCC on disk to refer to.
As has been said, maintenance plans are better than nothing, but that's not saying much. If Ola's scripts don't do it for you, I'd invest the time and write your own.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
January 7, 2016 at 11:21 am
My advice is to dump maintenance plans.
The two word solution is - Ola Hallengren
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 7, 2016 at 12:20 pm
I only use Maintenance Plans for backups (data and transaction log) as well as file cleanup. They work pretty well for that. (For what I need anyways).
I also concur that doing the indexing with the maintenance plan is not a good idea. I found that it sometimes didn't actually do something when it needed to for different tables. I am using the indexing script that the others have referenced and it is really awesome. It is so customizable and once you get it set up the way you want, is very effective.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply