Log file Growth

Question

Log file Growth

VastSQL

SSC-Dedicated

Points: 36097
More actions
August 1, 2016 at 6:10 am

#328762

Hi Experts,
Have observed that the log file is growing huge because of Index Rebuild operation? Can anyone help me understand why?
TIA

Viewing 15 posts - 1 through 15 (of 19 total)

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

VastSQL (8/1/2016)
Hi Experts,
Have observed that the log file is growing huge because of Index Rebuild operation? Can anyone help me understand why?
TIA

Have a look at Choosing a Recovery Model for Index Operations

😎

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

Yup, frequently.

In full recovery model, index rebuilds are 'size of data' operations, which means that the entire thing gets logged. A rebuild of a 10GB index will require more than 10GB of log space.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

VastSQL (8/1/2016)
Hi Experts,
Have observed that the log file is growing huge because of Index Rebuild operation? Can anyone help me understand why?
TIA

My question is how are you doing your index maintenance? If using a Maintenance Plan - DON'T DO THAT!! They are horrible for numerous reasons. Go get ola.hallengren.com's free, awesome, documented maintenance suite!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

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

GilaMonster (8/1/2016)
Yup, frequently.
In full recovery model, index rebuilds are 'size of data' operations, which means that the entire thing gets logged. A rebuild of a 10GB index will require more than 10GB of log space.

Thanks Gail.

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

Eirikur Eiriksson (8/1/2016)
VastSQL (8/1/2016)
Hi Experts,
Have observed that the log file is growing huge because of Index Rebuild operation? Can anyone help me understand why?
TIA
Have a look at Choosing a Recovery Model for Index Operations
😎

Hi Eirikur, we have full recovery mode with every one hour log backup.

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

TheSQLGuru (8/1/2016)
VastSQL (8/1/2016)
Hi Experts,
Have observed that the log file is growing huge because of Index Rebuild operation? Can anyone help me understand why?
TIA
My question is how are you doing your index maintenance? If using a Maintenance Plan - DON'T DO THAT!! They are horrible for numerous reasons. Go get ola.hallengren.com's free, awesome, documented maintenance suite!!

We have custom script for rebuild. We never had issue before and this is the first time we are experincing this

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

VastSQL (8/2/2016)
Eirikur Eiriksson (8/1/2016)
VastSQL (8/1/2016)
Hi Experts,
Have observed that the log file is growing huge because of Index Rebuild operation? Can anyone help me understand why?
TIA
Have a look at Choosing a Recovery Model for Index Operations
😎
Hi Eirikur, we have full recovery mode with every one hour log backup.

Have you considered increasing the frequency of the log backup? Most likely will lessen the load on the log.

😎

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

Eirikur Eiriksson (8/2/2016)
VastSQL (8/2/2016)
Eirikur Eiriksson (8/1/2016)
VastSQL (8/1/2016)
Hi Experts,
Have observed that the log file is growing huge because of Index Rebuild operation? Can anyone help me understand why?
TIA
Have a look at Choosing a Recovery Model for Index Operations
😎
Hi Eirikur, we have full recovery mode with every one hour log backup.
Have you considered increasing the frequency of the log backup? Most likely will lessen the load on the log.
😎

We dont have the space to keep that many log backups and also our RPO is actually 2 hours.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 9

VastSQL (8/2/2016)
Eirikur Eiriksson (8/2/2016)
VastSQL (8/2/2016)
Eirikur Eiriksson (8/1/2016)
VastSQL (8/1/2016)
Hi Experts,
Have observed that the log file is growing huge because of Index Rebuild operation? Can anyone help me understand why?
TIA
Have a look at Choosing a Recovery Model for Index Operations
😎
Hi Eirikur, we have full recovery mode with every one hour log backup.
Have you considered increasing the frequency of the log backup? Most likely will lessen the load on the log.
😎
We dont have the space to keep that many log backups and also our RPO is actually 2 hours.

Shouldn't really affect the space needed as the logs will be smaller.

😎

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

Eirikur Eiriksson (8/2/2016)
VastSQL (8/2/2016)
Eirikur Eiriksson (8/2/2016)
VastSQL (8/2/2016)
Eirikur Eiriksson (8/1/2016)
VastSQL (8/1/2016)
Hi Experts,
Have observed that the log file is growing huge because of Index Rebuild operation? Can anyone help me understand why?
TIA
Have a look at Choosing a Recovery Model for Index Operations
😎
Hi Eirikur, we have full recovery mode with every one hour log backup.
Have you considered increasing the frequency of the log backup? Most likely will lessen the load on the log.
😎
We dont have the space to keep that many log backups and also our RPO is actually 2 hours.
Shouldn't really affect the space needed as the logs will be smaller.
😎

Thanks Eirikur. sys.databases also not showing the need for further log backup

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

GilaMonster (8/1/2016)
Yup, frequently.
In full recovery model, index rebuilds are 'size of data' operations, which means that the entire thing gets logged. A rebuild of a 10GB index will require more than 10GB of log space.

Why this much space is required, when you say entire things get logged what all get logged and why its not releasing space once the index rebuild completes successfully?

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

The entire new index gets logged. The space does get released once the rebuild finishes, into the log file for reuse (because if that much space was required once, it'll probably be required again).

The log file doesn't automatically shrink though, which is what you seem to be expecting. No database file does.

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 13

GilaMonster (8/3/2016)
The entire new index gets logged. The space does get released once the rebuild finishes, into the log file for reuse (because if that much space was required once, it'll probably be required again).
The log file doesn't automatically shrink though, which is what you seem to be expecting. No database file does.

Thanks Gail , understood. Now what confuses me is that the size of the index involved in rebuild comes around 5GB only.

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

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