PRB: A Timeout Occurs When a Database Is Automatically Expanding (305635)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
This article was previously published under Q305635

SYMPTOMS

You may experience the following symptoms in SQL Server:
  • A database does not expand automatically although you have the autogrow option on.
  • The Errorlog file does not contain any messages that indicate that the database is full.
  • Client computers that try to modify data in this database receive a "Timeout Expired" message on their user transaction.
  • SQL Profiler displays an "Attention" event on a connection, and, if the computer is running SQL Server 7.0, SQL Profiler also displays an "Exception 822" error.
NOTE: The "Timeout Expired" message is a generic message that you may receive in other situations.

You may notice these symptoms when:
  • You run SQL Profiler for a long time, you capture the Exception event and the IntegerData column, and then you look for an occurrence of the "Exception 822" error.

    -Or-
  • You use the -y822 parameter to start SQL Server. After the database does not automatically expand, you check the Errorlog file for a stack dump because of the "Exception 822" error.
NOTE: The database file may automatically expand, but the timeout occurs when the file is being initialized for use, which must occur for the database to be useful.

CAUSE

When a connection issues a Data Modification Lanaguage (DML) statement that requires additional space in a database, SQL Server tries to automatically expand the database, if you have the autogrow option set. The client that issued the statement may time out while the database is expanding, depending on the query timeout value that is configured on the client side. Several applications use a default timeout value of 30 seconds. This timeout ends the client connection and the automatic expansion of the database that it prompted.

WORKAROUND

To work around this behavior, use one of the following methods:
  • Expand the database manually. To expand the database manually, use either the ALTER DATABASE statement or SQL Server Enterprise Manager.
  • Increase the client query timeout value of the application that requests the DML so that the server has time to expand the database. The DML prompts the database to expand.

    For example, if you are using SQL Query Analyzer, the default query timeout value is 0 (infinite). If you are using the default value, you do not experience this behavior. If you are not using the default value, increase the query timeout value.

    For other applications (whether they are Web-based or not), you can make the change in the application. For example, if you are using ADO, you can change the CommandTimeout property for the Connection or Command object.
  • Determine how long the database expansion takes on your server. To determine this, run the ALTER DATABASE statement in SQL Query Analyzer, and then view the status bar. After you determine this, set the client timeout value to something greater than this value.
  • Do not change the client timeout value. Continue to test the automatic expansion and see how much expansion completes in less than the client timeout value. Set the autogrow option to this new (smaller) value.

MORE INFORMATION

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

315512 INF: Considerations for Autogrow and Autoshrink Configuration

Modification Type:MinorLast Reviewed:12/2/2005
Keywords:kbprb KB305635
©2004 Microsoft Corporation. All rights reserved.