This article helps you resolve the problem that occurs when you try to modify a large table by using the table designer in SQL Server Management Studio.

    Original product version:   SQL Server

    Original KB number:   915849

    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.

        Note

        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 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.

    procedure:SQL Server Management Studio-->tools->options-->Designers->transation time-out after

    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:

    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.

SQL Server Management Studio提示“超时时间已到。在操作完成之前超时时间已过或服务器未响应”

    一、出现场景及症状

    1.修改数据库结构且数据非常多数据时

    2.更新数据表查询操作费时太久时

    ALTER TABLE table_name

    ALTER COLUMN column_name datatype

    出现超过问题，主要是因为数据量大，导致未更新完就超过了sql的执行时间。

    二、解决办法：

    在SQL Server Management Studio中工具,按如下设置一下，

    就不会提示“超时时间已到。在操作完成之前超时时间已过或服务器未响应”

    将超时时间由100秒改成更长的时间，如1000秒

    关闭后再打开，就解决了。