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 Saturday, January 9, 2010 2:45 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:06 AM
Points: 176, Visits: 728
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
Post #844957
Posted Saturday, January 9, 2010 4:58 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:57 AM
Points: 5,888, Visits: 13,062
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.


---------------------------------------------------------------------

Post #844972
Posted Saturday, January 9, 2010 5:39 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 9:31 AM
Points: 717, Visits: 3,037
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
Post #844973
Posted Sunday, January 10, 2010 4:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 6, 2010 3:27 AM
Points: 7, 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.
Post #845026
Posted Sunday, January 10, 2010 4:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 30, 2012 8:34 AM
Points: 2, 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.
Post #845029
Posted Sunday, January 10, 2010 5:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:38 PM
Points: 31,368, Visits: 15,834
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
Post #845146
Posted Sunday, January 10, 2010 7:06 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 18,064, Visits: 16,099
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
Post #845155
Posted Sunday, January 10, 2010 7:25 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 6:56 AM
Points: 6,804, Visits: 1,934
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
Post #845156
Posted Sunday, January 10, 2010 7:30 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:47 PM
Points: 587, Visits: 1,998
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
Post #845158
Posted Monday, January 11, 2010 1:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 30, 2012 8:34 AM
Points: 2, Visits: 19
Mani,

Consider using Ola Hallengren's scripts: http://ola.hallengren.com/
Post #845217
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse