InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 21:02:20 GMT20http://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxGlad 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).Sat, 23 Jan 2010 12:46:50 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxAs a solo DBA with 10 years more exp in other DBs and with less than a dozen servers with relatively small (&lt;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... ;-)RichTue, 12 Jan 2010 08:56:26 GMTrichj-826679http://www.sqlservercentral.com/Forums/Topic844957-263-1.aspx[quote][b]Brad M. McGehee (1/11/2010)[/b][hr][quote][b]tibor_karaszi (1/11/2010)[/b][hr]Mani,Consider using Ola Hallengren's scripts: http://ola.hallengren.com/[/quote]I often incorporate some of Ola's scripts inside my Maintenance Plans, running them as "Execute SQL Server Agent Job" tasks.[/quote]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.Mon, 11 Jan 2010 14:33:15 GMTJeffrey Williams 3188http://www.sqlservercentral.com/Forums/Topic844957-263-1.aspx[quote][b]tibor_karaszi (1/11/2010)[/b][hr]Mani,Consider using Ola Hallengren's scripts: http://ola.hallengren.com/[/quote]I often incorporate some of Ola's scripts inside my Maintenance Plans, running them as "Execute SQL Server Agent Job" tasks.Mon, 11 Jan 2010 13:57:02 GMTbradmcgehee@hotmail.comhttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspx[quote]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.[/quote]The Maintenance Plan uses the following default T-SQL code to perform integrity checks of databases.DBCC CHECKDB(N'database_name') WITH NO_INFOMSGSSo, 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.Mon, 11 Jan 2010 13:55:22 GMTbradmcgehee@hotmail.comhttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxI'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.Mon, 11 Jan 2010 08:00:53 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxMani,Consider using Ola Hallengren's scripts: http://ola.hallengren.com/Mon, 11 Jan 2010 01:26:03 GMTtibor_karaszihttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxHi,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.[quote]-- Ensure a USE statement has been executed first.SET NOCOUNT ON;DECLARE @objectid int;DECLARE @indexid int;DECLARE @partitioncount bigint;DECLARE @schemaname nvarchar(130);DECLARE @objectname nvarchar(130);DECLARE @indexname nvarchar(130);DECLARE @partitionnum bigint;DECLARE @partitions bigint;DECLARE @frag float;DECLARE @command nvarchar(4000);-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function-- and convert object and index IDs to names.SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS fragINTO #work_to_doFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')WHERE avg_fragmentation_in_percent &gt; 10.0 AND index_id &gt; 0;-- Declare the cursor for the list of partitions to be processed.DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;-- Open the cursor.OPEN partitions;-- Loop through the partitions.WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS &lt; 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid;-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag &lt; 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag &gt;= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount &gt; 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END;-- Close and deallocate the cursor.CLOSE partitions;DEALLOCATE partitions;-- Drop the temporary table.DROP TABLE #work_to_do;GO[/quote]What is the T-sql script for Update statistics?thanksManiSun, 10 Jan 2010 19:30:05 GMTMani-584606http://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxIm glad to see this posted. I work on many servers where maintenance plans are 'good enough', especially for low intensity clients. There are definitely times when something more powerful is needed, but even that can often be done via SSIS and the maintenance tasks rather than true custom code - either way, a good DBA uses the right tool for the job. Even if it's not glamourous!Sun, 10 Jan 2010 19:25:09 GMTAndy Warrenhttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxGood editorial. I agree with the do what you can principle and then add to it by committing to make it better.Sun, 10 Jan 2010 19:06:28 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxNice editorial, Brad, and glad to see you've changed your mind. I always recommend maintenance plans as a start. They work 80% of the time, and they are better than nothing. If you know how to do more, you should, but at least having a maintenance plan is a good idea.What I'd like to see is these set up automatically on servers and databases, unless someone turns them off. That way at least some maintenance is being done.Sun, 10 Jan 2010 17:49:47 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxHi Brad,Having taught SQL Server for a very long time now, I very much have the same experience and stance. Fact is that many DBAs just won't code - at ltest not today. So, dismissing maint plans will just leave those servers without maintenance. I nowadays, while teaching, do spend some time on maint plans, how it is implemented, what each task does and try to be as neutral as possible about it. Of course, part of this includes explaining the downsides where there is such.Sun, 10 Jan 2010 04:51:26 GMTtibor_karaszihttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxThanks for making this available. As a part-time DBA I agree with your message only too well. It's hard to find the time to improve, even though the will is there, and I'm glad you're sharing your experience - targeted at my kind of audience with the aim to help, and not condescendingly. If the ebook is as good as it sounds, I'll be sure to buy a hard copy.Sun, 10 Jan 2010 04:33:08 GMTTom Juergenshttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxBrad, one of the best ways I used to learn Visual Basic for Applications years ago was to record macros and look at the code. It's still something I recommend to beginners.Much the same can be said for the various wizards and "Script As..." buttons in SSMS for SQL Server. Wizards will never be 100% accurate, but they're great for getting a beginner most of the way there.Looks like the e-book isn't yet available for download?Thanks for your open-minded reversal - that's probably the best lesson an IT professional can impart!!RichSat, 09 Jan 2010 17:39:29 GMTrmechaberhttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxI think you are correct to change your stance Brad. Whilst it is true you can do harm if are not aware of the effect of what you are doing, that is the fault of the person involved, not the maintenance plans. The same sort of mistakes would be made (e.g. constant file shrinking) if the person wrote their own code to maintain the databases, they would just be putting more effort into making those mistakes.Maintenance plans have their limitations but if they fit the bill for a database why not use them? For many databases out there nothing more than what they offer is required. For me its all part of the KISS principle.Sat, 09 Jan 2010 16:58:54 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic844957-263-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Editorial/69256/">A time and a place for the SQL Server Maintenance Plan Wizard</A>[/B]Sat, 09 Jan 2010 14:45:37 GMTbradmcgehee@hotmail.com