timeout issue

Question

timeout issue

halifaxdal

SSCoach

Points: 19741
More actions
January 5, 2017 at 8:42 am

#317336

I am on 2008R2, trying to update an existing table (70M rows) with a new ID as primary key.
I do have the timeout setting as 0 which presumably means no time-out.
I got time out error message within 1 minute.
What am I missing here?
What's the best way to add primary key to existing large size table?
Thank you very much.
January 9, 2017 at 9:49 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

halifaxdal (1/5/2017)
I am on 2008R2, trying to update an existing table (70M rows) with a new ID as primary key.
I do have the timeout setting as 0 which presumably means no time-out.
I got time out error message within 1 minute.
What am I missing here?
What's the best way to add primary key to existing large size table?
Thank you very much.

Are you do this using a query or are you doing this through the GUI? (think it's called table designer). That's my first guess, that you are using the GUI to do this. And that isn't how you would want to do this.

Some other things could be:

Have you monitored for locking, blocking when doing this?

Is space available in the data file?

Are there currently any clustered indexes on the table?

Sue

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I agree with Sue. You should do all this through T-SQL. If you prefer, you can use the GUI to build the code and use the bottom in the top left that says "Generate Change Script".

If you insist on doing everything through the GUI, there's an option that overrides the timeout setting in the Designers tab.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLisAwesome SSC-Addicted Points: 428 More actions · Answer 3

In case you need to use the designer, the time out settings are under the Designers, not SQL Server.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 4

Is there already a clustered index on the table?

If not, and ID is an identity value, are you sure it's best to cluster the table on ID?? What do the current missing index stats and index usage stats show for that table?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".