INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

understanding index rebuild

understanding index rebuild

(OP)
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.

RE: understanding index rebuild

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

CODE

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

CODE

alter index indexname on table rebuild 
tends to be much easier on the transaction log, but does cause concurrency issues (blocking).

RE: understanding index rebuild

(OP)
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';

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close