SQL Server 2008 R2 - Timeout After 30 Seconds - Can't Change
Question
Wednesday, July 18, 2012 9:15 PM | 2 votes
Hi there,
I'm experiencing a transaction timeout issue with SQL Server 2008 R2 (64 Bit).
I'm trying to change the value of a key in one row in a table (15,000,000 rows) using SQL Server Management Studio. I do this by selecting "Edit Top 200 Rows" on the table. Modify the query to find the row I want to change. Change the data in a key column and arrow down to save the row. After 30 seconds I get a timeout error message window.
==========================================================================================
Here are the error details in the error Microsoft SQL Server Management Studio window.
No row was updated.
The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
Correct the errors and retry or press ESC to cancel the change(s).
In the SSMS Options menu window I have the Table Options for Designers - Table and Database Designers - Set
to the following:
Checked On - Override connection string time-out value for table. (Have also tried checked off)
Transaction time-out after: set to 65535 seconds.
No matter what I change the time-out after to it always times out after 30 seconds.
It may be important to note that I'm logged directly into the SQL Server.
Is there another timeout setting I have to change to allow my data change above to take all the time it requires to complete it's task? If so, how do I change it?
All replies (14)
Thursday, July 19, 2012 3:33 PM ✅Answered | 1 vote
Hi Ross,
This behavior occurs because of the transaction time-out setting for the table designer and for the database designer in SQL Server Management Studio. You can specify this setting in the Transaction time-out after box. By default, this setting is 30 seconds.
Open SQL Server Management Studio, click Tools and select Options, then click Dsesigners, you would find Table and Database Disigners. You could set it.
To resolve this behavior, use one of the following methods:
• Click to clear the Override connection string time-out value for table designer updates check box for the table designer and for the database designer in SQL Server Management Studio.
• Specify a high setting in the Transaction time-out after box for the table designer and for the database designer in SQL Server Management Studio.
•Modify the large table by using Transact-SQL statements in Query Editor in SQL Server Management Studio.
For more information, please refer to http://support.microsoft.com/kb/915849.
TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.
Thanks,
Maggie
Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
Saturday, July 28, 2012 11:37 AM ✅Answered | 1 vote
Hi Ross,
Any progress?
This link could be helpful: http://blogs.msdn.com/b/ialonso/archive/2007/12/04/sql-execution-error-timeout-expired-the-timeout-period-elapsed-while-opening-view-from-ssms.aspx.
Thanks,
Maggie
Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
Wednesday, July 18, 2012 10:52 PM | 3 votes
Try to do it from Query windows by t-sql script. Error saying that data in row 1 not commited. Editing data from GUI is not a good idea.
Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!
Thursday, July 19, 2012 1:17 AM | 1 vote
I agree with Rohit.
Yeah, it should work from the GUI, but maybe it's eating a more informative message.
If need be you can edit the connection properties and change the timeout (for a grid? I think, I can't check it here and can't recall ever actually trying it!)
Josh
--
oops - nope, cannot find parameter for grid as a connection, see Maggie's answer below.
Thursday, July 19, 2012 1:20 PM
Thanks for the replies Rohit & Josh. Even though I can use T-SQL I would still like to know where this other timeout setting is. I have searched everywhere and I can't find it.
Regards
Ross
Ross Bevin
Thursday, July 19, 2012 1:57 PM | 1 vote
In database server properties there is a query wait. -1 value shows unlimited.
Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com
Thursday, July 19, 2012 2:20 PM
Thanks for the reply Shamas. I have Query Wait set to 1000 and I still get a timeout after 30 seconds.
regards
Ross
Ross Bevin
Thursday, July 19, 2012 2:35 PM
Is your database is Online and in healthy state and you are running your query from SSMS on same machine.
Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com
Thursday, July 19, 2012 3:34 PM
Hi Shamas,
Yes to all 3 questions!
regards
Ross
Ross Bevin
Thursday, July 19, 2012 5:44 PM
Hi Maggie,
Thanks for your reply.
I have already done as you have suggested. The Override connection string timeout is unchecked and the timeout is set to 65535. Even with these set I still get timed out after 30 seconds when I try and change one cell on the row of returned rows. I know I can use T-SQL but I don't want to have to in this case.
Regards
Ross
Ross Bevin
Wednesday, July 25, 2012 9:34 AM
Check your system resource and be sure that your are using SSMS on same server.
Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com
Wednesday, July 25, 2012 2:35 PM
Hi Shamas,
I have and I am. It still times out after 30 seconds.
Ross
Ross Bevin
Thursday, August 9, 2012 2:05 PM
Hi Maggie,
Sorry for the slow reply; I just got back from vacation.
This link you sent did indeed have the solution to my problem. I will repeat it here so that others following this thread can see it. The following registry key setting was set to 30 even after I had changed all the timeout sessions I could via SSMS. I set it to 65535 and I no longer get timed out.
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\DataProject\SQLQueryTimeout was set to 30.
Thanks so much Maggie!!!
Regards
Ross
Ross Bevin
Monday, August 12, 2013 10:51 PM
Hi Ross,
You might have also overlooked a deadlock. You can't alter a table via T-SQL or GUI if a deadlock emerged somewhere withing the table premise. I would strongly recommend that any TRAN should be committed or rolled back, if you're not sure what script did the locking. Detaching and Attaching of the MDF would be your last resort.
Thanks,
Joe Anthony Ortega