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