1

I know this kind of question has been asked previously but I don't get the answer that I'm looking for.

I'm reconsidering setting up Autogrowth for DB Primary & Data files for Log(partitioned table) and Log Files. I am going through some articles and what I conclude is:

Microsoft is suggesting Autogrowth to 1/8th of file size which is 12.5% on another hand Brent Ozar and others are suggesting 256MB for data files and 128MB for log files.

Now, I'm confused as my main database file is approximate 545GB and Logfile is around 330GB, and there will be big difference (either too big or too small) if I chose any option.

Currently, Autogrowth is set to 500MB for main data file, Logfile is set to 10% and for other NDF files Autogrowth is set to 5MB and 64MB

What would you recommend and how can I calculate it?

1 Answer 1

0

What would you recommend and how can I calculate it?

  • Don't ever use percentage growth settings, rather set it to a fixed value. Increase the fixed value later on if needed. But typically this doesn't need to be changed often. The reason being is as the file grows in size, percentage growth will take more and more space to grow each time a growth operation occurs, which will gradually take longer to complete. Growth operations are relatively heavy of an operation.

  • 1/8th or 1/4th are fine choices for the growth size, there's no exact answer here. But again, don't use percentage growth, set a fixed value based on the initial size of the file. For example, if you initially set the data file to 1,000 MB, then set the growth to a fixed amount of 250 MB.

  • I personally like to initialize the Log file to half the initial size of the Data file. So if my Data file starts at 1,000 MB (with a 250 MB growth amount), then my Log file will start at 500 MB (with a 125 MB growth amount).

  • Again, growth operations are somewhat heavy. If utmost performance is critical, then initializing the database files to the total space available on the disk(s) is another viable strategy. I'd still set the autogrowth settings to a reasonable fixed amount (based on whatever I would've set them to if I didn't pre-grow the files all the way). If you do this, you'll want to make sure to monitor the internally consumed space of the database files to be able to proactively determine when to add more space on disk. You can use sp_spaceused to monitor the consumed space.

my main database file is approximate 545GB and Logfile is around 330GB

That's roughly reasonable for relative sizes. Again, just make sure to switch the autogrowth settings to fixed amounts for the Log files.

there will be big difference (either too big or too small) if I chose any option.

The options are suggestions for how to loosely initially provision the database files sizes. Your database isn't expect to always stay at 256 MB big.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.