best practice or Standards for Maintanance task planing

Question

Post reply

best practice or Standards for Maintanance task planing

FTdenali

SSCertifiable

Points: 5362
More actions
August 22, 2011 at 1:13 am

#242498

hi,
I need the basic standards or rules or steps to follow when creating a maintanace plan.
1)Firstly which one comes first between dbcc checkdb, Full backup, rebuild index, backup trn logs?
2)firstly how often should you perform dbcc checkdb, rebuild index?
3)Is there a Microsoft universal statndards for this?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

I never do anything other than backups in a maintenance plan and occasionally I dont do that

FTdenali SSCertifiable Points: 5362 More actions · Answer 2

FTdenali

SSCertifiable

Points: 5362

August 22, 2011 at 3:03 am

#1371292

hi,

Let's exclude db backup.

FTdenali SSCertifiable Points: 5362 More actions · Answer 3

OK, LET'S exclude db backup and only ral about the rest, butstill which one comes first between DBCC CheckDb and Reindexing?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It doesn't matter. Either works.

I prefer checkDB first, I don't want to waste time rebuilding indexes if the database is corrupt. I certainly don't want to back up a corrupt DB.

That said, I almost never use maintenance plans for index rebuilds, especially on larger databases.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

FTdenali SSCertifiable Points: 5362 More actions · Answer 5

My thoughts exactly, Why i was asking this is because im in a new company and most of the Maintanace task have been setup in a very questionable way.

Thanks for the input.

Divine Flame SSCoach Points: 15943 More actions · Answer 6

THE-FHA (8/22/2011)
hi,
I need the basic standards or rules or steps to follow when creating a maintanace plan.
1)Firstly which one comes first between dbcc checkdb, Full backup, rebuild index, backup trn logs?
2)firstly how often should you perform dbcc checkdb, rebuild index?
3)Is there a Microsoft universal statndards for this?

1. I don't think you will stop your tran log backups when you are doing the re-indexing, so no connection between these two.

2. We should rebuild/reorganize indexes only when it is required i.e. when they are fragmented. You can use the DMV sys.dm_db_index_physical_stats to check the fragmentation level.

Below given script is available on msdn to identify those indexes which are fragmented up to a certain level & then automatically reorganize/rebuild them:

-- Ensure a USE <databasename> 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

You can read more here.

Sujeet Singh

FTdenali SSCertifiable Points: 5362 More actions · Answer 7

Hi,

This was a very useful script and i intend to run it on weekend.

thanks a lot.

Divine Flame SSCoach Points: 15943 More actions · Answer 8

Divine Flame

SSCoach

Points: 15943

December 6, 2011 at 5:50 am

#1417274

You are most welcome :-).

Sujeet Singh