Log file Growth

Question

Log file Growth

Viewing 5 posts - 16 through 19 (of 19 total)

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 1

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 .

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Twice as much space as you need for all your indexes - is that a big problem? What is the autogrowth increment for the log file? Was there any other activity on the database at the same time, just before or just after? You might find this useful.

John

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

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

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

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.

SeniorITGuy SSC Eights! Points: 864 More actions · Answer 5

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