SQLTeam.com | Weblogs | Forums

Opinions Please: File Size vs Growth Size


#1

I recently took over some servers and noticed that there were a number of database files with a size in the hundreds of Gigabytes and a Growth rate of either 100MB (sic) or set for 10%. I'm in the process of detecting and correcting but wanted to solicit opinions on what appropriate numbers might be. As a gross first cut I'm looking for any file bigger than 20GB but with a growth rate of less than 512MB or using a percentage. All constructive inputs would be welcomed.


#2

I typically use 256, 512 or 1024 for log growths. For data file growths, I'll go bigger if instant file initialization is configured and if the database size warrants it.


#3

While you're at it, you should check how many VLFs there are and fix those if they are high.


#4

Just like what Tara said, look at the VLF, normally the log files won't growth that much after its stabled for a while.
As for Data, using a fix size is always better then a percentage, as you can predict the size better rather then an unknown growth size every time (more for your capacity planning).


#5

Absolutely...

Instant file initialization is turned on. We do try to right size the initial files setting but we're often at the mercy of available information and third party default configurations but what else is new...

After some analysis most of the files are data; not log. I did find some 200GB files with a growth rate of 1% (ouch!). I'm planning on using a 4GB growth rate for the data files.


#6

Use 4000MB for the growth rate instead of 4GB. There's a bug that I can't recall the details about, but you want to avoid multiples of 4GB. Let me see if I can find it.


#7

Apparently it's fixed in 2012+:http://www.sqlskills.com/blogs/paul/bug-log-file-growth-broken-for-multiples-of-4gb/

I think I'd still use 4000MB just in case the bug rears its ugly head again, which does happen to bugs sometimes.


#8

Thanks for the tip on the 4GB bug. I had a number of log files with that growth size and they had crazy VLF counts.


#9

I learned about the bug after a painful maintenance window where we rebooted the database server. An extremely mission critical database took 45 minutes to complete recovery. We contacted Microsoft the following day to figure out what happened. That's when I learned about VLFs and then also about the bug. I believe our growth size was 4GB or 8GB. It was a VLDB. The large log file had around 50,000 VLFs. YIKES.

This was many years ago. I've always kept my eye on VLFs and made sure not to use multiples of 4 for the growth size.


#10

Not to one up you but... I had one yesterday just shy of 80K. Zounds!