Do you trust Maintenance Plans?

Question

Do you trust Maintenance Plans?

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

johnzabroski SSCrazy Points: 2355 More actions · Answer 1

I don't trust Maintenace Plans.

For one, when you try to view the source code, the preview comes with a disclaimer that the source code might not actually be what gets executed. Seriously???

For two, Maintenace Plans for integrity checks have reported DBCC CHECKDB took 0.0 seconds in the job history logs, but they never show up in the SQL Server logs. Well, if it doesn't show up in the Server logs when it is supposed to, some seriously stupid stuff must be going on. And guess what? It is a black box so you have NO idea why you are screwed if something is corrupted. And this was a SILENT failure.

After I stopped trying to figure out why, I just deleted these stupid Maintenance Plans and went back to handrolling them. I can't believe we ever bothered to use these!

For three, Maintenance Plans will fail on edge cases, and that is why many people appear to be using Ola Hallengren's maintenance scripts[/url] instead.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 2

We use maintenance plans for backups, and they work fine, but for index maintenance and CHECKDB we use self written T-SQL.

SQLMD Right there with Babe Points: 764 More actions · Answer 3

Add one more for using a hybrid. We us Maintenance plans on our 10 SQL 2005 databases and we have custom scripts for the index maintenance.

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

I have been using MPs for many years, even on SQL Server 2008 R2. If there were any issues, that were due to things external to the MPs. Just like the experts echoed, I use the hybrid approach whenever required. I trust them.

Thank You,

Best Regards,

SQLBuddy.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 5

I've used them, often as a base maintenance setup for systems that aren't stressed or critical. They have worked fine.

However when I need finer control, I tend to use custom scripts to handle things. There is a great backup script[/url] from Ola Hallengren and a nice maintenance script[/url] from Michelle Ufford that are worth looking at.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

Steve Jones - SSC Editor (12/11/2010)
I've used them, often as a base maintenance setup for systems that aren't stressed or critical. They have worked fine.
However when I need finer control, I tend to use custom scripts to handle things. There is a great backup script[/url] from Ola Hallengren and a nice maintenance script[/url] from Michelle Ufford that are worth looking at.

I use similar scripts in maintenance plans, when I need to.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 7

bethrich (11/18/2010)
In SQL 2008 SP1 (not R2), running in VM with a NAS, I have intermittent failures in the reporting step of the MPs. The job fails, though the task actually succeeds.
This seems very rare in the community, though others have seen it. I don't have the problem in my servers running 2000 SP4 or 2005 SP3.

Just curious but are any of those servers not having this problem run on a NAS as the SQL 2008 instance does?

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

johnzabroski SSCrazy Points: 2355 More actions · Answer 8

Jeffrey Williams-493691 (12/11/2010)
Steve Jones - SSC Editor (12/11/2010)
I've used them, often as a base maintenance setup for systems that aren't stressed or critical. They have worked fine.
However when I need finer control, I tend to use custom scripts to handle things. There is a great backup script[/url] from Ola Hallengren and a nice maintenance script[/url] from Michelle Ufford that are worth looking at.
I use similar scripts in maintenance plans, when I need to.

But when is when? For example, I've stated real issues I've had with using maintenance plans.

"When I need to" is too vague. Please be more certain; we all benefit.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 9

johnzabroski (12/13/2010)
Jeffrey Williams-493691 (12/11/2010)
I use similar scripts in maintenance plans, when I need to.
But when is when? For example, I've stated real issues I've had with using maintenance plans.
"When I need to" is too vague. Please be more certain; we all benefit.

I believe it all depends on your environment. I have worked with SQL Server 2000 maintenance plans and could not stand them. SQL 2005 I began to like them a little better but for some task it just does not meet the requirements. It is almost comparable to HA solutions, each one has a place depending on the environments and requirements.

I have heard Buck Woody speak on creating a maintenance plan for task, but not setting a schedule on them. He would then create a SQL Agent job and call the maintenance plan through it, giving him more control on when each task was done. That is just his preference, it does not mean it works for everyone.

I have had issues with maintenance plans working and not working. When it gets to that point I usually drop it cause I can get the same task to work in a SQL Agent job quicker than trying to figure out why the maintenance plan did not run. As well maintenance plans can get botched during service pack or hot fix installs easier (at least with me 😀 ).

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

bethrich SSC Veteran Points: 288 More actions · Answer 10

Shawn Melton (12/12/2010)
bethrich (11/18/2010)
In SQL 2008 SP1 (not R2), running in VM with a NAS, I have intermittent failures in the reporting step of the MPs. The job fails, though the task actually succeeds.
This seems very rare in the community, though others have seen it. I don't have the problem in my servers running 2000 SP4 or 2005 SP3.
Just curious but are any of those servers not having this problem run on a NAS as the SQL 2008 instance does?

Shawn, We have quite a number of 2000 and 2005 instances running on VM +NAS. No MP problems except SQL 2008. All servers have the same AV. It's something unique to SQL 2008. I've started rolling SP2 and the AV just had an upgrade, all the same day unfortunately. No failures since that last one I posted.

I've rolled my own for years and really would prefer to keep doing so -- old Sybase habits die hard. 🙂

Beth Richards
Sybase, Oracle and MSSQL DBA