Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 915849 - Last Review: March 31, 2006 - Revision: 1.1
Error message when you try to modify a large table by using SQL Server Management Studio: "Timeout expired"
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.
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.
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:
This behavior is by
design.
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:
- Create a temporary table with the same table schema.
- Copy all the data from the actual table to the temporary
table.
- Drop the actual table.
- Rename the temporary table to the name of the actual table.
APPLIES TO
- 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 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate