April 2, 2007 at 8:42 am
I get a timeout creating an index on a fairly large table using MGT Studio (something I don't normally do - I typically run a SQL command). There are 121,761,428 rows in the table. This table is not being used by anyone. While I am creating the index the CPU spikes and runs consistently at 50 to 90 percent. The timeout comes about 5 minutes after I start it. There does NOT appear to be excessive disk queuing.
I am running SQL 2005 on sp2 on 2003 Enterprise sp1. Dual core. 6GB of memory allocated to SQL. Our disk is a ISCI SAN. I have 2 volumes for 1 for data and 1 for logs.
My question is ... I've never gotten timeouts creating an index before - is this timeout a by-product of the new and "improved" interface? Does anybody have a feeling for why this is happening?
FYI ... I did run a script rather than go thru the GUI and it finished after 27 minutes.
April 3, 2007 at 2:15 am
A couple of things for you to check...
Firstly, when you create your database connection File>Connect Object Explorer or clicking the Connect button in the Object Explorer. Click the "Options >>" button and check the Execution time-out value.
Secondly, there is a Time-out setting in Management Studio for transactions performed whilst in the Database or Table designer. This can be found under Tools->Options. Expand the Designers node and click on Table and Database Designers. Enable the "Override connection string time-out value for table designer updates" and set a high value for the number of seconds.
HTH.
April 3, 2007 at 6:37 am
Thanks for the response!
Here are my settings currently:
Connection Properties->Execution time-out is set to 0.
Tools->Options->Query Execution->Execution time-out is set to 0.
April 3, 2007 at 6:46 am
The "Query Execution" setting is used when you run a query using T-SQL in the query window. However, if you are using the table designer (right clicking a table name and choosing Modify) to create your index, then a different setting is used for the time-out.
The setting is under "Tools > Options > Designers > Table and Database Designers" called "Override connection string time-out value for table designer updates". Make sure this is enabled and set a high value for the "Transaction time-out after:" setting.
April 3, 2007 at 8:59 am
much thanks ... I see what you are talking about now. I unchecked it and let the index create run for a while ... it didn't time out. so I stopped it after 7 minutes - long after it normally would have timed out on me. I'm going to call this: Mystery Solved. Thanks for your help.
August 5, 2016 at 12:03 pm
I has the same issue. GUI was failing with timeout during Index creation. Create Index script and it worked fine!
thanks
Mitch Small
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy