January 9, 2010 at 2:45 pm
Comments posted to this topic are about the item A time and a place for the SQL Server Maintenance Plan Wizard
Brad M. McGehee
DBA
January 9, 2010 at 4:58 pm
I 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.
---------------------------------------------------------------------
January 9, 2010 at 5:39 pm
Brad, 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!!
Rich
January 10, 2010 at 4:33 am
Thanks 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.
January 10, 2010 at 4:51 am
Hi 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.
January 10, 2010 at 5:49 pm
Nice 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.
January 10, 2010 at 7:06 pm
Good editorial. I agree with the do what you can principle and then add to it by committing to make it better.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 10, 2010 at 7:25 pm
Im 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!
January 10, 2010 at 7:30 pm
Hi,
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.
-- 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 frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 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 < 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 < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 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
What is the T-sql script for Update statistics?
thanks
Mani
January 11, 2010 at 1:26 am
Mani,
Consider using Ola Hallengren's scripts: http://ola.hallengren.com/
January 11, 2010 at 8:00 am
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
January 11, 2010 at 1:55 pm
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
January 11, 2010 at 1:57 pm
tibor_karaszi (1/11/2010)
Consider using Ola Hallengren's scripts: http://ola.hallengren.com/%5B/quote%5D
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
January 11, 2010 at 2:33 pm
Brad M. McGehee (1/11/2010)
tibor_karaszi (1/11/2010)
Mani,Consider using Ola Hallengren's scripts: http://ola.hallengren.com/%5B/quote%5D
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
“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
January 12, 2010 at 8:56 am
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply