understanding index rebuild

[niall5098]

HI There,

I have an index rebuild job that runs on a weekly basis against 1 database. The database itself is 25GB but when the job runs the tlog file expands to 120GB. Why does the log file grow by at least 5 times the size of the actual database. i queried the index sixes but none stand out as extremely large.

all responses welcome.

 

[yelworcm]

How are the indexes being rebuilt? Is it by a maintenance plan, or a home grown script? Do you happen to know what commamd is used for the rebuild?

If the rebuild is being done by

alter index indexname on table reorganize

this can cause a lot of transaction log entries, as each record in the index is recorded as a separate log entry. Using

alter index indexname on table rebuild

tends to be much easier on the transaction log, but does cause concurrency issues (blocking).

 

[niall5098]

its actually an execute tsql step in a maintenance, the script is from online but the main part is:

IF (@indexname IS NOT NULL AND @DB_Name IS NOT NULL AND @schemaname IS NOT NULL AND @objectname IS NOT NULL AND @AllowRowLocks = 1) -- if [allow_row_locks] = 0, indexing will fail, these indexes cannot be processed.
BEGIN
IF @frag < 30.0
BEGIN;
SELECT @command = 'ALTER INDEX [' + @indexname + '] ON [' + @DB_Name + '].[' + @schemaname + '].[' + @objectname + '] REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
pRINT >> Executing >> ' + ISNULL(@command,'..Empty..')
EXEC (@command);
END;
IF @frag >= 30.0
BEGIN;
SELECT @command = 'ALTER INDEX [' + @indexname +'] ON [' + @DB_Name + '].[' + @schemaname + '].[' + @objectname + '] REBUILD';