If you are like me, you use the SSMS GUI for various things.  Though, I tend to use scripts for the management of most things, I do check on the database growth rates of files using the GUI interface.

While I was recently,  doing a review of a client’s environment I discovered that the GUI can lie to you when it comes to the database file growth rates.  By default, the data file is set to a 1MB growth rate and the log file is configured for a 10% growth rate.  Both are horrible settings for most OLTP environments.  However, starting with SQL Server 2016, the default growth rates are configured for 64MB, which in my opinion is better than the previous defaults.

Using the GUI to look at a 2017 Scratch database I have, we can see that the data file is configured for 1MB and the log file is set for 64MB growth.

Spoiler Alert:  The GUI is lying.

If we look into the DMV sys.master_files (sys.database_files will also show this), we can see that the growth rate is set to 16 for the data file and 8192 for the log file.  The documentation tells us that these numbers represent the number of 8K pages, rounded to the nearest 64 kilobytes.  Time for a little math!  Let’s look at the log file first.

The DMV states that the growth is 8192 8K pages.  8192 * 8 = 65,536 kilobytes, which divided by 1024 will result in 64 megabytes.  Awesome.  The resulting answer matches what is shown in the GUI.  We are good there.  

Now let’s look at the data file and reveal the lies.

The DMV states that the growth rate is 16 8K pages.  16 * 8 = 128 kilobytes, which divided by 1024 will result in 0.125 megabytes.   Wait.  Hold the phone.  The GUI depicted that the growth rate was 1 megabtye.  The math isn’t adding up to what the GUI tells us.

After some research, I was able to determine that the answer for this discrepancy is in the T-SQL command, ALTER DATABASE.  This command allows you to modify certain aspects of the database, include file growth rates.  If you look at the documentation, notice that the growth rates can be specified in kilobytes, megabytes, gigabytes, terabytes, or percent.

This means that you can specify the file growth in kilobytes with a command such this one:

ALTER DATABASE [Scratch] MODIFY FILE (NAME = Scratch, FILEGROWTH = 512KB)

This command would specify that the file growth rate for the data file, Scratch, is 512KB.  After running the command on the Scratch database, we can see that the growth (remember: number of 8K pages) has changed to 64.

Let’s do the math.  64 * 8 = 512 kilobytes.  Ok, that’s the expected number since the command specified 512 kilobytes of growth.   However, looking once again at the GUI, it still reflects 1MB!

The moral of the story?  Don’t always believe the GUI.  It can sometimes lie to you and cause issues.  Double check values with other means such as DMVs.

In the case of my client, they had a number of databases that were manually configured to a smaller growth rate of 1MB and given they are a highly transactional organization, this was just causing issues for them creating more growth events and effecting performance.

Important:  In order to grow the files, SQL Server has to take an exclusive lock on the files which will just stop everything in it’s tracks until it completes.  Granted, it should complete quickly, however with a large transaction, it might have to grow many times within the transaction.  Don’t ever do this.

© 2018, John Morehouse. All rights reserved.