Click here to monitor SSC
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
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
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 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
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 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
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4465 Visits: 9833
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 Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 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
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10765 Visits: 12019
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