Additional diagnostics added to diagnose long-running or canceled database autogrow operations in SQL Server

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:

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: 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 of MB.
• 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: