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
bradmcgehee@hotmail.com
bradmcgehee@hotmail.com
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1256 Visits: 730
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
george sibbald
george sibbald
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: 25406 Visits: 13701
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.

---------------------------------------------------------------------
Rich Mechaber
Rich Mechaber
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2801 Visits: 3671
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
Tom Juergens
Tom Juergens
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 116
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.
tibor_karaszi
tibor_karaszi
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 19
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: Administrators
Points: 149973 Visits: 19449
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68319 Visits: 18570
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

Andy Warren
Andy Warren
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: Moderators
Points: 25903 Visits: 2747
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!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Mani-584606
Mani-584606
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2672 Visits: 1998
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
tibor_karaszi
tibor_karaszi
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 19
Mani,

Consider using Ola Hallengren's scripts: http://ola.hallengren.com/
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