Timeout expired when altering table

Question

Timeout expired when altering table

kerobin jesu

SSC-Addicted

Points: 494
More actions
October 22, 2007 at 6:47 am

#119053

Hi all
I need to change a table datatype from int to bigint which has 1million records... i dropped all the indexes except clustered index.. but it is timing out when i tried to modify the table... can anyone please help me to modify the table...
Thanks in Advance!
- Kerobin

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55454 More actions · Answer 1

Are you doing it through the GUI ?? I think I remember that in SQL 2000 I would get timeouts using the GUI because it had a timeout setting that got reached, whereas scripting out the command, then running it in a query window it ran fine. Not sure if 2005 has the same issue. Or perhaps there's a lock on the table ?

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

homebrew01 (10/22/2007)
Are you doing it through the GUI ?? I think I remember that in SQL 2000 I would get timeouts using the GUI because it had a timeout setting that got reached, whereas scripting out the command, then running it in a query window it ran fine. Not sure if 2005 has the same issue. Or perhaps there's a lock on the table ?

Ironically, I just ran into this this morning on '05. I guess this is MS's way of telling us that we should be scripting it out anyway.. :hehe:

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

James Horsley Hall of Fame Points: 3196 More actions · Answer 3

Yeah - altering a table with a million records through the GUI = brave man

James Horsley
Workflow Consulting Limited

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 4

Hey!!!!!!!!! script the table using Database publishing wizard which gives you both the schema & objects (values). Drop the exisiting table. Re-design the index and do the rest of the modifictions. If you are admin, remotely access the server. Get into the query analyzer... Run through the script. This will solve the issue.

abmore Hall of Fame Points: 3628 More actions · Answer 5

Change the timeout expire limit & try to alter the table schema by query

🙂

shashi kant SSCommitted Points: 1659 More actions · Answer 6

Try through using CUI not through GUI

ex.

Alter Table Person

Alter Column Person_Add nVarchar(100) not null

in my example Person_add is having a datatype varchar(100)

so i modify it

regards

shashi kant chauhan

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 7

Doing it from GUI on a table with 1 million records will definitely take a toll. Well in 2005 also if you alter the table through management studio then it creates a new table and then copies the entire data to the new table and drops the old table(same like sql server 2000). So it will defnitely timeout.

Do it through the query and it will not time out.

aureolin Hall of Fame Points: 3520 More actions · Answer 8

Personally I prefer to script these sorts of commands, because I keep the scripts as a log of the changes I've made to the database. Do it through the GUI and you're depending on your memory to "know" when something changed. In my case (at least) that's not something I want to depend on... 😀

Steve G.

Tariq Changgez Old Hand Points: 399 More actions · Answer 9

I also faced similar problem where I was using GUI to alter a column in my table. Since this table has a VarBinary(Max) column which stores large documents, I was not able to alter any field using the Management Studio.

After reading this post from Shashi Kant I altered the column through TSQL, and it did the job in a blink of an eye.

Thanks for your great hint.:-)

Tariq Changgez