You are currently offline, waiting for your internet to reconnect

Error message when you try to modify a large table by using SQL Server Management Studio: "Timeout expired"

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

SYMPTOMS
When you try to modify a large table by using the table designer in Microsoft SQL Server Management Studio, you may receive an error message that is similar to the following:
- Unable to modify table.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
CAUSE
This behavior occurs because of the transaction time-out setting for the table designer and for the database designer in SQL Server Management Studio. You can specify this setting in the Transaction time-out after box. By default, this setting is 30 seconds.

Notice that this setting differs from the setting in the Execution time-out box in SQL Server Management Studio. By default, the setting in the Execution time-out box for Query Editor in SQL Server Management Studio is zero. By default, the setting in the Query time-out (seconds) box for Query Editor in Microsoft SQL Server 2000 SQL Query Analyzer is also zero. Therefore, Query Editor waits infinitely for the query to finish and never times out.
RESOLUTION
To resolve this behavior, use one of the following methods:
  • Click to clear the Override connection string time-out value for table designer updates check box for the table designer and for the database designer in SQL Server Management Studio.
  • Specify a high setting in the Transaction time-out after box for the table designer and for the database designer in SQL Server Management Studio.
  • Modify the large table by using Transact-SQL statements in Query Editor in SQL Server Management Studio.
For more information about these settings, visit the following Microsoft Developer Network (MSDN) Web site:
STATUS
This behavior is by design.
MORE INFORMATION
The modification of a large table may be time-consuming. This is because SQL Server must perform the following actions when you try to modify the table schema:
  1. Create a temporary table with the same table schema.
  2. Copy all the data from the actual table to the temporary table.
  3. Drop the actual table.
  4. Rename the temporary table to the name of the actual table.
Properties

Article ID: 915849 - Last Review: 03/31/2006 19:27:26 - Revision: 1.1

Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbsql2005tool kbprb kbexpertiseadvanced KB915849
Feedback
content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">