Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

A time and a place for the SQL Server Maintenance Plan Wizard Expand / Collapse
Author
Message
Posted Monday, January 11, 2010 8:00 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #845405
Posted Monday, January 11, 2010 1:55 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723


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
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #845700
Posted Monday, January 11, 2010 1:57 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 7:20 AM
Points: 175, Visits: 723
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
Microsoft SQL Server MVP
Director of DBA Education, Red Gate Software
www.bradmcgehee.com
Post #845701
Posted Monday, January 11, 2010 2:33 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:48 PM
Points: 4,388, Visits: 9,506
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #845719
Posted Tuesday, January 12, 2010 8:56 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 4, 2014 2:10 PM
Points: 35, Visits: 151
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
Post #846253
Posted Saturday, January 23, 2010 12:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:33 PM
Points: 8,557, Visits: 9,051
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
Post #852595
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse