When a database file reaches its maximum allocated size, a growth event is triggered to expand the file and allow for more data storage. The size of these growth events can be configured in the database options or by using the ALTER DATABASE statement, as demonstrated below.
Properly managing these growth settings is critical to maintaining performance and avoiding issues like fragmentation and resource contention.
Managing Growth Sizes
For log (.ldf) files, careful consideration of growth sizes is important to avoid Virtual Log File (VLF) fragmentation. This can slow down database recovery and impact performance. For a deeper dive, Paul Randal’s post on this topic is an excellent resource.
While data (.mdf) files don’t face the same VLF concerns, setting growth sizes improperly can still lead to problems. Configuring growth increments that are too small can result in frequent growth events, causing fragmentation and unnecessary resource usage. On the other hand, overly large growth increments might result in delayed expansions or unplanned disk space alerts—particularly for Temp DB files.
Should You Use Percent Growth?
Using percent-based growth might seem like a logical choice, as it scales with the database size. However, it’s not always ideal. For large databases, waiting on gigabyte-sized growth events can degrade performance. Although enabling Instant File Initialization can reduce delays during data file growth, it doesn’t apply to log files, which must be zeroed out.
Many DBAs avoid percent-based growth entirely and instead configure fixed-size increments. Database growth sizes should be adjusted based on the database’s typical growth patterns and business requirements. For critical databases, measuring organic growth with a monitoring tool and scheduling controlled growth during maintenance periods can help minimize disruptions.
Monitoring Free Space and Growth
Keeping an eye on free space within your databases is essential. Using a monitoring solution makes this easier, but even without one, tools like Power BI can do the trick if that works for you. Below is an example of a Power BI report I made that visualizes disk space usage:
A few ideas anyway. That’s it for now!
Leave a Reply