Post reply

timeout issue

  • 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 5, 2017 at 11:30 am

    #1920950

    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

  • January 5, 2017 at 12:52 pm

    #1920966

    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

  • January 6, 2017 at 1:38 pm

    #1921199

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

  • January 9, 2017 at 11:55 am

    #1921469

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 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