THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Adjust autogrow setting for your database files

I very frequently see too small autogrow value for database files. Even the default in many cases. The defaults prior to SQL Server 2016 are 1 MB for data files and 10% for log files, adjusted to a little bit better values in 2016. One of the tings I often do when I start working on a SQL Server is to adjust the values to something more reasonable.

I have been wanting to automate this for a long time, but other things got in between. But now I have finally created my sp_dbfiles_autogrow procedure. (I'm sure I'm not the first one, but I wanted my own.) It creates and populates a table with the values to generate, based on the current size for the database file, and end result is that it outputs the ALTER DATABASE commands as text (using PRINT). I.e., it doesn't really change anything, only generate the commands. You can find the procedure here. All comments and bug findings are of course welcome!


Published Tuesday, December 20, 2016 11:41 AM by TiborKaraszi
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Avid Reader said:

Tibor, thx for the share. Quick question does it recommend a larger auto growth rate for mdfs vs ldfs? Not that I need it but just wondering.

January 4, 2017 12:00 PM

TiborKaraszi said:

Sorry for the late reply, it seems email notifications for comments are missing at the moment. If you have instance file initialization, then a data file can grow immensely quicker than a log file. So, yes, we can have a bigger autogrow for data files. I didn't want to complicate my script with this, in the name of Keep It Simple... :-)

February 2, 2017 9:09 AM

animal jam said:

The article you have shared here very good. This is really interesting information for me. Thanks for sharing!

July 18, 2017 2:48 AM

Ace2three said:

Valuable information! Looking forward to seeing your notes posted. Thank you for sharing the nice article. Good to see your article.

July 24, 2017 12:26 AM

symbolab said:

It is a great article. You will surely like this also because it is a great stuff, yeah it’s give us lots of interest and pleasure. Their opportunities are so fantastic and working style so speedy. Thank you for sharing the nice article.

July 24, 2017 7:21 AM

AVto-ma said:

It​ ​is​ ​a​ ​great​ ​article.​ ​You​ ​will​ ​surely​ ​like​ ​this​ ​also​ ​because​ ​it​ ​is​ ​a​ ​great​ ​stuff,​ ​yeah

it’s​ ​give​ ​us​ ​lots​ ​of​ ​interest​ ​and​ ​pleasure.​ ​Their​ ​opportunities​ ​are​ ​so​ ​fantastic​ ​and

working​ ​style​ ​so​ ​speedy.​ ​Thank​ ​you​ ​for​ ​sharing​ ​the​ ​nice​ ​article.

August 4, 2017 12:19 AM

gradeup said:

Really appreciate for your amazing article. Keep going on, good stuff. Thank you for this valuable information.

August 15, 2017 9:17 AM

jio plans said:

The information you have posted is very useful. Thank you for nice and wonderful Information.

August 15, 2017 9:18 AM

adassfghkjl said:

nice article useful and the theme seems good all the best to attract the users go for the light blue and if you need go to my site i mentioned here in my site i provide the good lookhave a look and reply me in comment bye..

August 15, 2017 10:41 AM

Leave a Comment


This Blog


Privacy Statement