When I tried to modify a table structure in with more than 20 million records. It gave me the time out expired error. This occurred in MS SQL Server 2005
The modifications were not saved.
When I initially tried to save the modifications for the table, a warning message appeared for me.
Later I clicked yes and again waited for the result. It finally gave me an error saying that, 'time out expired' after 30 seconds.
The error was "Errors were encountered during the save process. Some database objects were not saved" and "Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." The table structure was same as previous.
Reason for this is the time out setting in the designer option. It would be set to 30 seconds by default. If you changes take longer than that time then they would not be saved. We need to change the value of the setting.
For this, go to Tools -> Options -> Designers
There will be check box option of "Override connection time-out value for table designers updates:" and "Transaction time-out after:" change the default value of 30 secs to the a higher value. In my case I made it 1000 seconds. And then click on OK. Do remember that transaction time out setting value should be greater than 0 and it should be in the range between 1- 65535.
Now after doing this change, I tried to modify the table and the changes got saved now.
Note that while the table modifications are saved, the SQL Management studio would remain inaccessible till the changes to complete.
The modifications were not saved.
When I initially tried to save the modifications for the table, a warning message appeared for me.
Later I clicked yes and again waited for the result. It finally gave me an error saying that, 'time out expired' after 30 seconds.
The error was "Errors were encountered during the save process. Some database objects were not saved" and "Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." The table structure was same as previous.
Reason for this is the time out setting in the designer option. It would be set to 30 seconds by default. If you changes take longer than that time then they would not be saved. We need to change the value of the setting.
For this, go to Tools -> Options -> Designers
There will be check box option of "Override connection time-out value for table designers updates:" and "Transaction time-out after:" change the default value of 30 secs to the a higher value. In my case I made it 1000 seconds. And then click on OK. Do remember that transaction time out setting value should be greater than 0 and it should be in the range between 1- 65535.
Now after doing this change, I tried to modify the table and the changes got saved now.
Note that while the table modifications are saved, the SQL Management studio would remain inaccessible till the changes to complete.
No comments:
Post a Comment