October 1, 2007 at 11:56 am
This table has 1.7 million records. Try to add a column, hit save 3 times and it gave me a time-out error. Hit save the 4 time and it's ok.
anyone know why I got the time-out error?
October 1, 2007 at 12:05 pm
Dead lock.
Took longer than the connection time out.
Hard to tell. I might suggest you script those changes out so that you can run them manually and have more control on what's going on. I'm sure this could have been completed the first time, in far less than 1 sec.
October 1, 2007 at 12:24 pm
When you add a new column it does takes time to go through the pages locking people (connections) out allocating / splitting pages for the column space and updating all indexes. This opperation is supposed to be slow.
* Noel
October 1, 2007 at 12:25 pm
Did you add your columns at the end of the table, or between other fields?
If you added it at the end of the table, Management Studio will often issue an ALTER TABLE statement (you can see it by looking at the script). Sometimes it cannot because of other reasons. This is a huge improvement over Enterprise Manager which always did what I have in the next paragraph.
If you added it between other fields, Management Studio has to create a new table, copy all of your data into it, and then drop the old table and rename the new one. 1.7 million rows on a busy server could take some time. It also has a tendency to create the table, populate it, and then apply indexes - including a clustered index so it may need to re-sort all of your data again.
An ALTER TABLE will usually be much more efficient, but you don't get to put columns in a different order.
October 1, 2007 at 12:28 pm
May I suggest you NEVER, EVER use EM nor SSMS for DDL ? You want to be in control of what is actually going to happen. It does not take a long time to type ALTER TABLE ... ADD ....
Cheers,
* Noel
October 1, 2007 at 1:09 pm
Thank you so much DBAs.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply