Database Simplified Headline Animator

Database Simplified

Wednesday, 12 October 2011

Unable to modify table. Timeout expired In SQL Server

This notification is generally encountered when you are trying to modify a large table probably added a column in between or changing the datatype of one or more fields which are generally very costly operation and time consuming. You can avoid this message by applying any of methods below.

  • Start SSMS –> Goto Tools Menu –>Option –> Under Option Dialogue Choose Designers—>Table and Database Designers
    • Uncheck  “Override connection string time-out value for table designer update

image

  • Start SSMS –> Goto Tools Menu –>Option –> Under Option Dialogue Choose Designers—>Table and Database Designers
    • Specify the high value for “Transaction time-out after:

image

 

  • You Can Create Script Of Table Changes and Run It as Query. See Following Screen Shot To Generate Query For Table Changes

image

3 comments:

  1. "Create Script Of Table Changes and Run It as Query."

    Great suggestion!
    I never knew what that button was for.

    Since I got bit with trying to modify a table with a ton of records I tried extending the timeout to 5 minutes nad it still failed.

    I generated the script and ran it - it took 2 seconds!!!

    Not sure why the designer doesn't generate the same script under the hood and then run it!

    Thanks for the great tip.

    ReplyDelete
  2. I just generated and ran one of those change scripts and lots over 140 million records when it failed. YMMV.

    ReplyDelete
  3. All computerized reservations systems rely upon sophisticated databases of flight inventories and reservations. Agents also maintain client databases in traveler profiles.create dashboard for oracle

    ReplyDelete