July 10, 2012 at 8:58 pm
Hi All,
This is one question i have related to db maintenance.
Suppose if i have 50 indexes in my databases and i have created a maintenance plan to rebuild all my indexes in the database.
How much space is additional space is required for my rebuilds to be successful avoiding autogrowths of mdf and ldf?
How much space will be needed in MDF and how much i need for ldf. How to do rough estimations on this?
Also, how much do i need consider if i am going for online indexes?
How much additional space do i need to consider for tempdb ?
One more thing, if i select all the databases in rebuild maintenace plan i.e. all user db and all indexes and if for somereason the job failed for one database, does all my work is rollbacked ? is my work half done? how to react in such scenario's and what are best practices while rebuild index maintenance plan.
Appreciate if anyone can throw light on this... Any example scenario would be appreciated.
Thanks in advance.
July 10, 2012 at 9:43 pm
In the MDF you will need as much empty space as the largest index, probably a clustered index, and a bit more. If the largest table is 20 GB, you should probably plan on 25 GB of free space.
You will need at least that much free in the LDF, and maybe more, depending on how frequently your transaction log backups run and how long it takes to run a transaction log backup.
The rebuild of each individual index is a single transaction, so a rollback will only affect that index.
July 12, 2012 at 10:07 pm
Thanks Mike sharing your thoughts.
July 13, 2012 at 3:55 am
You can use option 'SORT_IN_TEMPDB = ON' in order to avoid unnesesary growing your user database files.
July 14, 2012 at 7:34 am
I think the general rule of the thumb is that rebuild of a clustered index requires 1.5 times the actual size.
As mentioned in an earlier reply you can use TempDB for sorting but that again would be subjective to each environment, i mean like whether you have a separate \efficient TempDB drive to optimize IO or if your TempDB is already configured to reduce contention by adding data files to match the no of processors or in general whether how stressed your TempDB may already be and so on.
if you are using snapshot isolation your TempDB may already be doing a bit of work with having to maintain different versions. and if you are using online re-indexing ( if you are with enterprise edition) then TempDB would already be used during the online indexing.
you can also quantify the requirement on a approximate basis(not a smart way to do it though) by observing growth in the data file (or index file if there is a separate one). you will observe the space used value while the index rebuild is in progress and then see space used value fall back after the index is rebuilt completely.
July 17, 2012 at 5:00 pm
you can change the recovery model from Full to Bulk-logged . In this case the rebuild process is minimally logged and it's so helpful reducing ldf growth.Also you can just defrag the indexes that has fragmentation more than a specific threshold.here is a scrip i have wrote to rebuild indexes on all my instance based on a threshold for fragmentation that I've been using for a month.If you change the tempdb ldf & mdf(s) to dedicated drive perhaps you can get a better performance.:
------------------------
USE [master]
GO
ALTER DATABASE [YAFForums] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO
USE [YAFForums]
SET NOCOUNT ON;
DECLARE @DB nvarchar(40)='YAFForums',@Threshold tinyint=10,@Table NVARCHAR(100),@Schema nvarchar(100),@Index NVARCHAR(256),@c CURSOR,@ExecStr NVARCHAR(512)
SET @C=CURSOR FAST_FORWARD FOR
SELECT OBJECT_NAME(ps.OBJECT_ID) TableName,s.name SchemaName,i.name Indexname FROM sys.dm_db_index_physical_stats(DB_ID(@DB),null,null,null,null) ps INNER JOIN sys.indexes i on ps.object_id=i.object_id and ps.index_id=i.index_id INNER JOIN sys.tables t on ps.object_id=t.object_id INNER JOIN sys.schemas s on t.schema_id=s.schema_id
WHERE (ps.avg_fragmentation_in_percent>@Threshold) and (i.index_id>0) and (ps.page_count>1000)
OPEN @C
FETCH NEXT FROM @C INTO @Table,@Schema,@Index
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @ExecStr=' ALTER INDEX ['+@Index+'] ON ['+@Schema+'].['+@Table+'] REBUILD WITH ( ONLINE = ON , SORT_IN_TEMPDB = ON)'
BEGIN TRY
EXEC (@ExecStr)
END TRY
BEGIN CATCH
SELECT @index IndexName, ERROR_MESSAGE() Error
END CATCH
FETCH NEXT FROM @C INTO @Table,@Schema,@Index
END
DEALLOCATE @C
GO
USE [master]
GO
ALTER DATABASE [YAFForums] SET RECOVERY FULL WITH NO_WAIT
GO
-------------------------
Pooyan
July 18, 2012 at 1:20 am
pooyan_pdm (7/17/2012)
you can change the recovery model from Full to Bulk-logged .
Keep in mind that will make it impossible to do "Point-in-Time" restores for any log file backups that occur during the Bulk-Logged period.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply