Understanding Database Autogrowth in SQL Server
We use SQL Server database Autogrowth setting to automate database file growth. This property is very helpful if you use it carefully with proper planning. But there can be a negative performance impact on your database if you don’t give attention on it and leave it with default values. If you have too many autogrow events in your database, it can degrade the overall performance of the database. Below are the points that you can consider to reduce the autogrow events in a database to improve performance.
Set Optimum Database Size
Your database should have enough size that it never grows and if it grows make sure that Instant file initialization should be enabled on that SQL Server Instance. Autogrowth is an option that should be used to avoid unexpected scenarios of space issues in database files. You should plan your database size in a way that can minimize the use of autogrowth setting. You should figure out how big your database will be over the time and set the database size to that value. Analyze database size on every few months to adjust the size if it requires to increase. This way you can minimize autogrow events to be performed on your database files.
Resize Database Autogrowth in SQL Server
Auto growth events are expensive operations that slow down the performance of your database because whenever an auto-growth event is performed, SQL Server holds up database processing. This equates to slower response time for those SQL commands that are being processing against the database that is growing.
If you run a large transaction that requires the log to grow, other transactions that require a write to the transaction log will also have to wait until the autogrow operation completes. If your database is performing frequent autogrow events, then you have a good scope of performance improvement by proper resizing the database autogrowth settings that will reduce the number of autogrow events to be occurred.
Another negative side of autogrowth is the disk fragmentation. The more autogrow events you have the more physical fragmentation you will have. When your database is physically fragmented, it will take more time for SQL Server to read that databases. To avoid the issues associated with auto-growth events you need to minimize the number of its occurrence.
Change Default Autogrowth Settings
As per my experience, never leave your database files with the default autogrowth settings. The autogrowth default value for data files is 1MB and log files is 10% of total size of the log file. These autogrowth values are inherited from model database. You can change the autogrowth size of each database file of model database that will apply on all databases we create on this SQL Server Instance.
For example: You can change autogrow settings from default value that is 1MB to one-eighth of the database file size. This will again reduce the total number of autogrow events of the database. Make sure that Instant file initialization should be enabled on that SQL Server Instance.
Monitoring
If you know your database growth pattern, then set autogrowth per that growth. If you don’t have any idea about your future database growth, then you should proactively monitor database file sizes and their autogrowth events for a week or month and then set it per the growth pattern. We should also proactively monitor database growth histories for all databases to avoid any future outages due to disk or database file full.
You can analyze database disk usage dashboard report to get the number of times autogrowth event has occurred for your database files. I have explained this later in this article.
Best Practices for Database Autogrowth in SQL Server
You can improve the database performance and can manage the disk space utilization by focusing on below four points. This will also reduce the number of Autogrowth events to be occurred in the database files.
- Analyze your database growth pattern and set optimum size of database with the analyzed growth settings. Your database should have enough size that it never grows and if it grows make sure that Instant file initialization should be enabled on that SQL Server Instance. This will drastically reduce the SQL Server Autogrowth events in day to day life.
- Proactively monitor database file sizes and their autogrowth events. This helps you to analyze the growth pattern of database files and avoid fragmentation.
- Consider defragmenting your database file system if you have lot of auto-growth events occurred on your databases.
- Never leave your database autogrowth with default values, change it as per growth pattern that you analyzed over time.
- Avoid using autogrowth in percentage, rather give a specific amount of size in MB/GB. Microsoft suggests to set your autogrow setting to about one-eighth the size of the file and test it before deploying on PROD.
- Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.
- The growth increment of your transaction log must be large enough to stay ahead of the needs of your transaction units. Even with autogrow turned on, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query.
Change Database Autogrowth in SQL Server?
You can run below command to change the autogrowth value of any database file.
--Pass logical filename of your database file for under NAME section. ALTER DATABASE [Techyaz] MODIFY FILE ( NAME = N'Techyaz_Data', FILEGROWTH = 10000KB )
You can also change the autogrowth settings using GUI in SSMS. Launch the database property window. Click on files tab from left side pane and then on the three dots (…) that are showing besides every database file. You can see this window in below screenshot.
How to View Total Number of Autogrow Events Occurred on a Database?
You can find the number of autogrowth events occurred for your database by running “Disk Usage” dashboard report. Right click on your database for which you want to see total number of autogrowth events. Choose “Reports” and then select “Disk Usage” as shown in below image.
You will get below dashboard report for disk usage. You can see all details regarding space usage. Auto growth events are also captured in the same report. I have highlighted those entries in below screenshot.
Once you expend the plus + sign, you can see total number of autogrow events that have been occurred for your database. You can count the total numbers and total space as well. Based on this analysis you can alter your autogrowth settings to the optimum value that will reduce that much numbers as it is showing here.
I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.
Reference: MSDN Article based on Autogrowth
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
1 Response
[…] Understanding Database Autogrowth in SQL Server […]