February 29, 2008 at 2:23 pm
Hello All,
I am upgrading our enviroment from SQL 2000 to 2005. The database size (one of them) is about 40Gb. Tables have millions records. I needed to change the field size for one of the tables and got time out error in SQL 2005, was unable to save changes at all. The same change worked in 2000 (took about 45 minutes to complete but it did it!)
Then I tried to add the new column to the same table (to move the data there and delete the old column). Didn't work either! Time out error and not saving.
Here is my question: how to modify large tables in SQL 2005? Is there any way of extending the time out setting? where to change it? I was using just table desicner to do the change. Didn't try if could use T-SQL to update the table. Does anyone esle had same problems?
Thanks for any info!
February 29, 2008 at 2:31 pm
There is a way to change your timeout but have you tried to do a bulk insert into a new table (where the data types are updated)? Once the insert is complete, you can then rename the new table with the old tables name. That's one of the methods I seen used. Especially helpful when having to reorganize column order. Just be sure you copy all indexes, triggers, etc.
February 29, 2008 at 8:18 pm
The GUI will time out regularly when modifying large tables. T-SQL is the best way to make modifications to large tables.
Shane's suggestion is good as well. Although I might do so far as to say you should script the existing table, edit the script to enable the changes, then load the new table from the old table and go through the rename process.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 1, 2008 at 6:21 am
I agree with Jack. I should of mentioned that part about scripting out the current table. Good catch Jack.
March 1, 2008 at 5:53 pm
Thank you very much! I hope t-sql will work. Those tables are part of the application and they are coded somehow there so if I create the table separately, the application can't see it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply