The default autogrow and autoshrink settings will work for you with no tuning on many SQL Server systems. However, there are environments where you do not have to turn the settings on or where you may have to adjust the autogrow and autoshrink parameters. This article gives you some background information to guide you when you select the settings for your environment.
Here are some things to consider if you decide to tune your autogrow and autoshrink parameters.
How do I configure the settings?
You can configure the autogrow and autoshrink settings by using one of the following:
An ALTER DATABASE statement
SQL Server Management Studio
The sp_dboption stored procedure (deprecated in SQL Server 2005)
Note For more information about how to set these settings in SQL Server 2005, visit the following Microsoft Developer Network (MSDN) Web sites:
You can also configure the autogrow option when you create a database.
To view the current settings, run the following Transact-SQL command:
sp_helpdb [ [ @dbname= ] 'name' ]
Keep in mind that the autogrow settings are per file. Therefore, you have to set them in at least two places for each database (one for the primary data file and one for the primary log file). If you have multiple data and/or log files, you must set the options on each file. Depending on your environment, you may end with different settings for each database file.
What are the performance implications?
If you run a transaction that requires more log space than is available, and you have turned on the autogrow option for the transaction log of that database, then the time it takes the transaction to complete will include the time it takes the transaction log to grow by the configured amount. If the growth increment is large or there is some other factor that causes it to take a long time, the query in which you open the transaction might fail because of a timeout error. The same sort of issue can result from an autogrow of the data portion of your database. To change your autogrow configuration, see the "ALTER DATABASE" topic in SQL Server Books Online.
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 grow operation completes.
If you combine the autogrow and autoshrink options, you might create unnecessary overhead. Make sure that the thresholds that trigger the grow and shrink operations will not cause frequent up and down size changes. For example, you may run a transaction that causes the transaction log to grow by 100 MB by the time it commits. Some time after that the autoshrink starts and shrinks the transaction log by 100 MB. Then, you run the same transaction and it causes the transaction log to grow by 100 MB again. In that example, you are creating unnecessary overhead and potentially creating fragmentation of the log file, either of which can negatively affect performance.
Physical fragmentation from changing the size of the data or log files can have a severe affect on your performance. This is true whether you use the automatic settings or whether you manually grow and shrink the files frequently.
If you grow your database by small increments, or if you grow it and then shrink it, you can end up with disk fragmentation. Disk fragmentation can cause performance issues in some circumstances. A scenario of small growth increments can also reduce the performance on your system.
In SQL Server 2005 or in later versions, you can enable instant file initialization. Instant file initialization speeds up file allocations only for data files. Instant file initialization does not apply to log files.
If you have many file growths in your log files, you may have an excessively large number of virtual log files (VLF). This can lead to performance problems with database startup/online operations, replication, mirroring, and change data capture (CDC). Additionally, this can sometimes cause performance problems with data modifications.
For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.
You can use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.
AutoShrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); they must not be left unmanaged.
Your autogrow increment must be large enough to avoid the performance penalties listed in the previous section. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. A general rule of thumb to you can use for testing is to set your autogrow setting to about one-eight the size of the file.
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.
Keep the size of your transactions as small as possible to prevent unplanned file growth.
Why do I have to worry about disk space if size settings are automatically controlled?
The autogrow setting cannot grow the database size beyond the limits of the available disk space on the drives for which files are defined. Therefore, if you rely on the autogrow functionality to size your databases, you must still independently check your available hard disk space. The autogrow setting is also limited by the MAXSIZE parameter you select for each file. To reduce the possibility of running out of space, you can monitor the Performance Monitor counter SQL Server: Databases Object :Data File(s) Size (KB) and set up an alert for when the database reaches a certain size.
Unplanned growth of data or log files can take space that other applications expect to be available and might cause those other applications to experience problems.
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.
SQL Server does not constantly test for databases that have hit the configured threshold for autoshrink. Instead, it looks at the available databases and finds the first one that is configured to autoshrink. It checks that database and shrinks that database if needed. Then, it waits several minutes before checking the next database that is configured for autoshrink. In other words, SQL Server does not check all databases at once and shrink them all at once. It will work through the databases in a round robin fashion to stagger the load out over a period of time. Therefore, depending on how many databases on a particular SQL Server instance you have configured to autoshrink, it might take several hours from the time the database hits the threshold until it actually shrinks.
The following table provides more information on the products or tools that automatically check for this condition on your instance of SQL Server and the versions of the SQL Server product against which the rule is evaluated.
Product versions against which the rule is evaluated
System Center Advisor
SQL Server database file growth setting is more than the current available disk space
System Center Advisor determines whether any database in the instance SQL Server has auto-grow configured for the database files. If the resulting auto-grow increment is larger than the available space on the volume, System Center Advisor generates a warning for this SQL Server database. If you receive this warning from System Center Advisor, review the auto-grow settings for the affected files and take corrective action. You can resolve this situation by using one of the following methods:
Reduce the growth increment such that the next growth can happen on this volume.
Add more space to this volume.
Move this file to another volume that has available space for its growth.
SQL Server 2008 SQL Server 2008 R2
For more information about how to grow and shrink your database and log files, click the following article numbers to view the articles in the Microsoft Knowledge Base:
256650 How to shrink the SQL Server 7.0 transaction log
272318 Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
317375 A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
247751 BUG: Database maintenance plan does not shrink the database
305635 A timeout occurs when a database is automatically expanding
949523 The latency of a transactional replication is high in SQL Server 2005 when the value of the "Initial Size" property and the value of the Autogrowth property are small
For more information about database file initialization, visit the following Microsoft Developer Network (MSDN) Web site:
Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Express, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Express, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Workgroup, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Express, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard