THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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

Comments

 

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
 

Banggood said:

Thank you for bringing more information to this topic for me. I’m truly grateful and really impressed.  Absolutely this article is incredible.

http://ssapptricks.com/banggood/

April 1, 2017 9:23 AM
 

thilak said:

http://filmywaps.in/ I have enjoyed reading many of the articles and posts contained on the website, keep up the good work and hope to read some more interesting content in the future.

April 11, 2017 10:40 AM
 

Le Bon coin said:

http://ssapptricks.com/le-bon-coin/

apps26725@gmail.com

I loved the way you discuss the topic great work thanks for the sharing this valuable info.  I really enjoy reading this article.

May 8, 2017 1:34 AM
 

Le Bon coin said:

Le Bon coin

http://ssapptricks.com/le-bon-coin/

apps26725@gmail.com

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.

May 8, 2017 1:24 PM
 

Blocket said:

Blocket

vootapp80@gmail.com

http://ssapptricks.com/blocket/

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.

May 22, 2017 5:12 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Privacy Statement