When a database file gets full and it needs more space, a growth event will happen. The size of said database growth can be set within the database options, or can be done using the ALTER DATABASE statement as shown below.

When considering growth event sizes for log (.ldf) files, you may want to have a look at this post by Paul Randall to avoid VLF fragmentation.

We don’t need to worry about such things as much with the Data (.mdf) files though. There’s no perfect number to set it to, just try not make it too small or big…. no… wait. Let me explain.

If I say keep it in proportion to your database size, you might think that percent growth would be the way to go? Waiting on gigabyte sized growth events may cause performance problems when managing larger databases (Instant File Initialisation might mitigate that), or it could potentially lead to unexpected disk space alerts, particularly if configured on all TempDB files.

For the reasons above, some folks avoid using percent growth completely, setting growth to around the 250MB mark if the database isn’t tiny. If it’s a business-critical database, you could measure organic growth with the use of a monitoring tool, then grow the database out during maintenance periods. The screenshot below is how we set a new database size.

You could then track free space within each database on the server, with the help of your chosen monitoring solution. If you don’t have one of those, a simple PowerBI Report does the trick!

A few ideas anyway. That’s it for now!