INF: Considerations for Autogrow and Autoshrink configuration in SQL Server (315512)

MORE INFORMATION

How do I configure the settings?

1. You can configure the autogrow and autoshrink settings with:
   • An ALTER DATABASE statement (SQL Server 2000 only).
   • The sp_dboption stored procedure.
   • SQL Enterprise Manager.
   Note If you are using SQL Server 2005, use the SQL Server Management Studio instead of the 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:

   How to: Add Data or Log Files to a Database (SQL Server Management Studio)
   http://msdn2.microsoft.com/en-us/library/ms189253.aspx
   Database Properties (Files Page)
   http://msdn2.microsoft.com/en-us/library/ms180254.aspx

   You can also configure the autogrow option when you create a database.
   
   You can view the current settings through the database properties in SQL Enterprise Manager (SEM) or you can run this Transact-SQL command:

   sp_helpdb [ [ @dbname= ] 'name' ]

2. 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.

Best Practices

• 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.