The default autogrow
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
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
How do I configure the settings?
- You can configure the autogrow and autoshrink settings by using one of the following:
Note If you are using SQL Server 2005, use SQL Server Management Studio instead of SQL Enterprise Manager. 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.
- An ALTER DATABASE statement (not available in SQL Server 7.0)
- SQL Server Management Studio or SQL Enterprise Manager
- The sp_dboption stored procedure (deprecated in SQL Server 2005)
You can view the
current settings through the database properties in SQL Enterprise Manager
(SEM). Or, you can 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
- 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
- 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
- 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
- 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
- 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
- Turn on the <MAXSIZE> setting for each file to
prevent any one file from growing to a point where it uses up all available
- 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
- 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.
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:
How to shrink the SQL Server 7.0 transaction log
Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
Database maintenance plan does not shrink the database
A timeout occurs when a database is automatically expanding
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:
For more information about enabling instant file initialization, visit the following Microsoft Web site:
For more information about transaction log physical architecture, visit the following MSDN Web site:
SQL Server Books Online; topics: "Transaction
Log Physical Architecture"; "Shrinking the Transaction Log"
Article ID: 315512 - Last Review: September 17, 2011 - Revision: 8.0
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
- 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
|kbsqlsetup kbinfo KB315512|