Tuesday, April 13, 2010

Timeout expired. The timeout period elapsed prior to completion of the operation

Timeout expired. The timeout period elapsed prior to completion of the operation


This is by far my favorite error in Microsoft SQL Server 2008 to date!!!
Let us say that you are trying to modify an existing table using SQL server management studio. Using SSMS Object Explorer, you browse down to the Database, Tables down to the specific table. Right click on the table and select Modify. Next you find the field that needs to be renamed or maybe the size needs to be changed or worse the field needs to be a new data type (yes this does happen in REAL life all the time). Regardless you go ahead and make the change and then try to save your work and then BAMM!! You get this nice wonderful error. In my case I was trying to add a Primary key to a table when this happened. I have included screen shots and some description here.





'stock_price_historical2' table



- Unable to create index 'PK_stock_price_historical'.


Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.


Could not create constraint. See previous errors.
--Error 2

===================================

Next I get this fine dialog box show below with error description after it;



User canceled out of save dialog



(MS Visual Database Tools)

------------------------------


Program Location:


at Microsoft.SqlServer.Management.DataTools.Interop.IDTDocTool.Save(Object dsRef, String path, Boolean okToOverwrite)


at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.DatabaseDesignerNode.Save(VSSAVEFLAGS dwSave, String strSilentSaveAsName, IVsUIShell pIVsUIShell, IntPtr punkDocDataIntPtr, String& strMkDocumentNew, Int32& pfCanceled)

So how do you get around this issue. One option is to change the timeout limit under Tools - Options. In my case it is already set to 0 (infinite!)
 
This is what I have discovered when wrestling with the issue in SQL Server Management Studio. If you go ahead and make the change using the graphic user interface, then right before saving the change, you can go ahead and generate a SQL script that will encompass the modification that you are trying to make through SQL 2008 Management Studio. I know the real DBA'S as are saying, why even bother with this and use the TRANSACT SQL (TSQL) from the getgo. Good point and I am in agreement here 100%. However if you are new to these DBA tasks, this may be your only option.

Getting back to the point, I’m going to show you an example where I was trying to add a primary key to my table, a simple task that you would think, however I’m getting my favorite error so this is what I’m going to do next. I’m going to go to modify the table, right click and choose Set Primary Key as shown below.


I

Next instead of saving this change, I’m going to right click in the empty space and select Generate Change Script. This is also shown below so you can see this in action.



SQL server will then go ahead and generate the change script including all the necessary sql code that you need. You can either select the portion that you like or you can save the whole SQL code to a SQL script. In our case I have just highlighted the portion (ALTER TABLE) that I need.



Finally you can open up a new SQL window, paste the code as shown above and execute the bad boy!!Pretty cool to get around the Annoying Timeout error in my opinion. Hope that helps.

Tags:Timeout expired. The timeout period elapsed prior to completion of the operation

17 comments:

Anonymous said...

Thank you very much sir!

I had exactly the same problem and your way made my day!

Anonymous said...

Exactly what I was looking for! Thanks!

Anonymous said...

How about the problem that I encounter..
I have a table with lots of data.
When I open the table using

Select * From TableName

The data will show without any problem.BUT if I do this

Select * From TableName order by dateField

it will give me your favorite Timeout Expired.

Any solution for this?

Kash said...

You guys are welcome!!

On the order by issue, maybe you need an index on the order by field.

Thanks

Anonymous said...

Awesome!! Thanks
Best Regards

jlertle said...

Tools -> Options -> Designers -> "Override connection string time-out value for table designer updates:" and "Transaction time-out after:"

jlertle said...

Tools -> Options -> Designers -> "Override connection string time-out value for table designer updates:" and "Transaction time-out after:"

Anonymous said...

15 years working with SQL and never encountered this problem till now.

Much appreciated

Kash said...

Thank you guys for the comments and suggestions!

Anonymous said...

Thank you Kash! Just what I needed.

I also made the change jlertle suggested in his comment. I'm guessing that will stop these errors from occurring in the first place, ya?

Mauro Bignami said...

Thank you very much!!

Anonymous said...

Thanks, it worked!

Kash said...

I can glad it is helping you guys, check out our other sql sites:

http://sqlserver2008tutorial.com
http://sqlazuretutorials.com/
http://sqlserver2012tutorial.com/

Kash

Anonymous said...

Good Job Man!!

Anonymous said...

This was very helpful. Thank you.

Anonymous said...

Muy bien, me ayudo con mi problema :)

Anonymous said...

Спасибо, бро!