When you enable the autogrow option for a Microsoft SQL Server 2000 database, SQL Server automatically expands the database when additional space is required. The database growth occurs when the worker thread that is processing a client request determines that the database file or the log file is out of space. For large databases, the default file growth interval of 10 percent may result in autogrow operations that expand the database or the log file by hundreds of megabytes (MB) or more.
During the database autogrow operation, the worker thread that performs the autogrow operation may hold on to critical database resources, such as locks that the worker thread previously obtained when it processed the user query. Therefore, concurrency may be negatively affected while the autogrow operation is in progress. Because of this, you may notice blocking, application query time-outs, and other performance-related issues on the database.
When a database autogrow operation is long-running or canceled, SQL Server writes a warning message to the SQL Server error log that indicates the result of the autogrow operation.
For additional information about the latest service pack for Microsoft SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:
How to obtain the latest SQL Server 2000 service pack
After you install SQL Server 2000 Service Pack 4 (SP4), you may receive one of the following warning messages in the SQL Server error log, depending on the outcome of the database autogrow operation:
Autogrow of file 'Database File Name' in database
'Database Name' took Number milliseconds. Consider using ALTER
DATABASE to set a smaller FILEGROWTH for this file.
of file 'Database File Name' in database 'Database Name'
cancelled or timed out by user after Number
If the duration of the autogrow operation exceeds 60
seconds or if it is not successful, the warning messages are logged in the SQL Server error log. You can use these warning messages to help diagnose performance
problems that occur because of long-running autogrow operations or autogrow operations that are not successful.
The warning messages help database administrators easily identify large autogrow operations. If
the autogrow operation negatively affects the database performance, the database administrator can
manually increase the database file size.
If you use
the autogrow option to manage your database size and you receive the warning messages in the SQL Server error log, you should consider one of the following changes:
- Change the database's autogrow rate to grow by a fixed number
- If you specify the autogrow rate by using a percentage, use a smaller percentage value.
- Set the database's autogrow rate based on the query
time-out period that is used by the client applications and the speed that a file expands
in your operating environment. For example, assume that your client
application uses a time-out period of 30 seconds and your operating environment
takes one minute to create or extend a file by 600 MB at the rate
of 10 MB per second. Therefore, set a growth rate of less than 300 MB to prevent a time-out
during the autogrow operation.
If your typical query uses a significant part of the configured 30
second time-out period, you may have to decrease the growth interval to a much
smaller value, such as 100 MB, so that it does not affect your database
queries. Have a database administrator manually increase the database file size using a connection that has a sufficiently large query timeout so that the autogrow operation is not canceled.
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
INF: Considerations for autogrow and autoshrink configuration
PRB: A timeout occurs when a database is automatically expanding