Maintenance Plan

Question

Post reply

Maintenance Plan

Sam1980

SSC Enthusiast

Points: 128
More actions
September 15, 2010 at 11:11 am

#157204

I know the maintenance plan wizard doesn't take new database into account and doesn't give s the flexibility to rebuild/reorganize indexes dynamically. Are there any other drawbacks of this wizard?
Thanks in advance.

Viewing 15 posts - 1 through 14 (of 14 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

It can take new databases into account - just specify 'all user databases'

---------------------------------------------------------------------

SkyBVI SSCoach Points: 15128 More actions · Answer 2

@ sam

What version of sql server are you using?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Sam1980 SSC Enthusiast Points: 128 More actions · Answer 3

Sam1980

SSC Enthusiast

Points: 128

September 15, 2010 at 11:46 am

#1221570

am using 2005

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

There is always a TSQL script task in the MP. This would allow to you do any SQL Server Tasks that could be accomplished using TSQL Scripts.

Thank You,

Best Regards,

SQLBuddy

Sam1980 SSC Enthusiast Points: 128 More actions · Answer 5

Thanks, I was trying to come up with a list of things that aren't possible from MP. Our company is against using T-SQL scripts so need to convince them.

SkyBVI SSCoach Points: 15128 More actions · Answer 6

@ sam

Th drawbacks in maintenance plan in 2005 , I feel is the maintenance cleanup task.

It works randomly, sometimes it deletes my old backups regularly(acc to schedule), but sometimes it doesn't. The job runs successfully but when I see the old backups, they are not deleted as scheduled, I have to manually delete them.

Strangely the remedy in that case can be to delete that maintenance plan and again creating it.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 7

@ sam

The Maintenance Plan Wizard can't determine which indexes need to be rebuilt, and which ones don't need to be rebuilt, and therefore has to rebuild them all.

Also,

Maintenance Plans created with the Wizard cannot be scripted and moved to other SQL Server instances.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 8

The maintenance plan is going to run the same things T-SQL scripts will run. The only difference is it's compiled.

I would be interested to hear why you can't run T-SQL scripts in the company for this stuff. They are more flexible, can be documented, and even can include better logging if it's needed.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 9

Steve Jones - Editor (9/15/2010)
The maintenance plan is going to run the same things T-SQL scripts will run. The only difference is it's compiled.
I would be interested to hear why you can't run T-SQL scripts in the company for this stuff. They are more flexible, can be documented, and even can include better logging if it's needed.

To play devil's advocate here - there is nothing you can do with scripts/procedures/sql agent jobs that cannot also be done using maintenance plans.

If needed, you can always use the Execute SQL Task for everything in the maintenance plan and just call your own procedures/scripts.

However, there are a few things you can do with maintenance plans that cannot be easily done using agent jobs. For example, in a maintenance plan you can parallel process multiple tasks and setup very complex dependencies. There really is no way to do that with agent jobs.

BTW - stop using the Wizard...build your maintenance plans manually instead of relying on the wizard to build it for you. This way, you know exactly what tasks are being created and what the dependencies are between the tasks.

Although you cannot easily script out maintenance plans, you can import the plans into an SSIS package, modify the connection information and save a copy to a new server. This way, you get the shell of the plan and all you have to do is modify the specifics (e.g. backup folders, databases, etc...).

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

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

Shrink Database Task doesn't have the reorganize option avialable in SSMS and TSQL. It has only TRUNCATE ONLY and NO TRUNCATE options.

Thank You,

Best Regards,

SQLBuddy

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 11

sqlbuddy123 (9/15/2010)
Shrink Database Task doesn't have the reorganize option avialable in SSMS and TSQL. It has only TRUNCATE ONLY and NO TRUNCATE options.
Thank You,
Best Regards,
SQLBuddy

Well, I wouldn't know because I will never schedule a shrink database task or put that in a maintenance plan of any kind. I wouldn't even put it in an agent job - since it is not something that should ever be scheduled on a regular basis.

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

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

Well, I wouldn't know because I will never schedule a shrink database task or put that in a maintenance plan of any kind. I wouldn't even put it in an agent job - since it is not something that should ever be scheduled on a regular basis.
Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs

Everything has a purpose. There is nothing called Never. It all depends on the environment in which we work and the scenario.

Previously we used to have a huge data load on a db on thursday midnight and by the time we went to the office on Friday morning the TLog would fill the disk space completely.

Before I joined a company , there used to be a MP job that would shrink the TLog to prevent it from filling the disk space. Later I found that it would just truncate and had limited use. So I created a separe job that would accomplish the task until we resolved the issue.

BTW I was just mentioning one of the things that MP can't do. It doesn't mean that I'm recommending to do that.

Thank You,

Best Regards,

SQLBuddy.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 13

Okay, I see what you are saying. But, if that was required you could always use the execute sql task.

So, yes there are issues where certain built in tasks cannot perform the same as a script, but that does not mean it cannot be added to a maintenance plan.

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

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

Well said Jeffrey. Agreed. That's what I mentioned in my previous post.

There is always a TSQL script task in the MP. This would allow to you do any SQL Server Tasks that could be accomplished using TSQL Scripts.
Thank You,
Best Regards,
SQLBuddy
Thanks, I was trying to come up with a list of things that aren't possible from MP. Our company is against using T-SQL scripts so need to convince them.

That's the reason why I mentioned about that Shrink Database task and not TSQL part of implementation.

Thank You,

Best Regards,

SQLBuddy