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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply