SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A time and a place for the SQL Server Maintenance Plan Wizard


A time and a place for the SQL Server Maintenance Plan Wizard

Author
Message
GSquared
GSquared
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55759 Visits: 9730
I've been recommending the plans wizard to newbie DBAs for years. As long as you don't go too far wrong on it, it's far better than nothing. If you run into specific problems with it, it's a chance to learn how to deal with those, probably by scripting them, without having to dive in head-first and try to learn the whole thing all at once.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 730


I'm using maintenance plan for database Integrity check.Could you please tell me what the difference in performing Integrity check using T-sql code (dbcc checkdb (dbname) with no_infomsgs) & Maintenance plan task?

and also I'm using maintenance plan for Index rebuild & Update statistics. I tried to use the below T-sql script from BOL, but using this script, I need to run this by going to each database manually and not able to automate it to run for all databases automatically by creating job. So again, I turn back to maintenance plan for Index rebuild.



The Maintenance Plan uses the following default T-SQL code to perform integrity checks of databases.

DBCC CHECKDB(N'database_name') WITH NO_INFOMSGS

So, as you can see, this is the same code you describe above. What the Maintenance Plan Wizard/Designer does is to run this this for every database on a SQL Server instance you specify as part of the Plan.

This topic is covered in my new book.

Brad M. McGehee
DBA
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1200 Visits: 730
tibor_karaszi (1/11/2010)
Mani,

Consider using Ola Hallengren's scripts: http://ola.hallengren.com/


I often incorporate some of Ola's scripts inside my Maintenance Plans, running them as "Execute SQL Server Agent Job" tasks.

Brad M. McGehee
DBA
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18421 Visits: 10042
Brad M. McGehee (1/11/2010)
tibor_karaszi (1/11/2010)
Mani,

Consider using Ola Hallengren's scripts: http://ola.hallengren.com/


I often incorporate some of Ola's scripts inside my Maintenance Plans, running them as "Execute SQL Server Agent Job" tasks.


I do something similar - where I use Execute SQL Task instead. I can then use custom code and the tasks to get exactly the process I want.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

richj-826679
richj-826679
SSC-Enthusiastic
SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)SSC-Enthusiastic (117 reputation)

Group: General Forum Members
Points: 117 Visits: 152
As a solo DBA with 10 years more exp in other DBs and with less than a dozen servers with relatively small (<100GB total) and simple DBs, it makes sense for us to use Maintenance Plans -- at least for now. Should I get hit by the proverbial bus, I believe it's much more likely that someone else could easily identify, troubleshoot, and maintain the Maintenance Plans over scripts.

And if I can ever get our 2K5 servers upgraded from SP2 to SP3, the backups might actually work more consistently, but that's another thread... ;-)

Rich
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25427 Visits: 12494
Glad you changed your mind about the maintenance plan wizard - I would hate to be a raw new DBA without it.

I make very little use of the maintenance plan wizard, but that's not because I expect it do do harm (as someone else said, I could do the same harm with an SQL script - in fact I could do far more harm with an SQL script) or to restrict the work that the maintenance plans can do in any unobvious way: it's because using it is a manual process. With a whole pile of servers scattered round the world, many in places where the internet speed is not really adequate for interactive access from the other side of the world, plus a strong desire to get the maintenance jobs set up on initial installation of a new instance of our product without giving the installers scripts that they have to go through manually, I found that using the (SQL2K) maintenance plan wizard meant that too much work was unautomated.
But I did use the maintenance plan wizard to create (or amend) template SQL jobs; it's then trivial to generate T-Sql scripts from the data in MSDB on the template system to create the correct jobs (or amend the existing maintenance jobs) on a new system (all our custmers have the same set of schemas, so they all need the same maintenace jobs, roughly speaking).
For someone with a small number of servers and no high rate of new installations the maintenance plan wizard should be adequate for a large portion of the time. If I were in that situation I would use it. I might want to write some extra jobs, but even so it would save me work.
The only thing that I've wanted to use that the maintenance wizard didn't support was incremental backup, but the way NTBackup breaks the SQLS backup chain it's often not possible to use incremental backups anyway (and even without incremental backups you have to make sure SQL full backup runs right after AD backup if you want a genuine full recovery model on a multi-server domain).

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search