Article ID: 822641 - View products that this article applies to.
BUG #: 469739 (SQL Server 8.0)
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:
(http://support.microsoft.com/kb/290211/ )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.
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.
Autogrow of file 'Database File Name' in database 'Database Name' cancelled or timed out by user after Number milliseconds.
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:
For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/315512/ )INF: Considerations for autogrow and autoshrink configuration
(http://support.microsoft.com/kb/305635/ )PRB: A timeout occurs when a database is automatically expanding
Article ID: 822641 - Last Review: November 2, 2007 - Revision: 3.2