August 4, 2016 at 3:31 am
John Mitchell-245523 (8/4/2016)
What do you mean by "only"? What log file growth are you seeing? What other index rebuilds occur in the hour between the previous transaction log backup and the next?John
Hi John,
Log file size increased from 1-10GB after the index rebuild job and the size is indexes involved are 5GB all together, its a weekly job .
August 4, 2016 at 6:01 am
GilaMonster (8/1/2016)
A rebuild of a 10GB index will require more than 10GB of log space.
Because there's other log records involved, and any other concurrent activities, and log reservations, and...
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
August 4, 2016 at 6:12 am
GilaMonster (8/4/2016)
GilaMonster (8/1/2016)
A rebuild of a 10GB index will require more than 10GB of log space.Because there's other log records involved, and any other concurrent activities, and log reservations, and...
Thanks Gail.
August 9, 2016 at 6:49 am
VastSQL,
Another possibility for dealing with this issue is to follow advice from this MSDN article:
Specifically:
"Consider setting the SORT_IN_TEMPDB option to ON for the index operation. This separates the index transactions from the concurrent user transactions. The index transactions will be stored in the tempdb transaction log, and the concurrent user transactions will be stored in the transaction log of the user database. This allows for the transaction log of the user database to be truncated during the index operation if it is required. Additionally, if the tempdb log is not on the same disk as the user database log, the two logs are not competing for the same disk space."
Hope this helps!!
Jon
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply