SQL Server 2008 “Timeout expired” error and SP1

I recently came across another error when using SQL Server 2008.
I recently applied Service Pack 1 to my server’s installation of SQL Server 2008. I had not done any development since then using the full version of SQL Server on the server; instead, as you will note from my recent blog entries on the subject, I had been doing ASP.NET development that used only SQL Express on the local machine as a data store for ASP.NET’s built-in Membership/Roles functionality.

The error

Today, I started some proof-of-concept work, using an old database, rather than wasting time creating a new database for a project that wouldn’t go into production.
Surprise, surprise! When I tried to open up the old database in Visual Studio 2010 using the Server Explorer, I was greeted with the following error message after a substantial wait:


Connect to Database Engine

__________________________
Cannot connect to [my servername].
_______________________________
ADDITIONAL INFORMATION:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

A long, wasted effort to Google up a fix ensued. I went down many false paths and dead ends, looking at things like the firewall on the server, database permissions, etc. One of the many things I checked and double-checked was that I had properly set SQL Server to allow remote connections.

The fix

I finally decide to search once more on the exact error message—which I had tried first, but unsuccessfully—and  eventually Googled my way to this page in Microsoft’s Knowledge Base. The actual Microsoft content was useless; I had already tried everything listed, or realized it didn’t apply to my case.
However, Microsoft, in their “infinite wisdom”, added a feature called “Community Content” a few years back. This feature allows users to add their own fixes/workarounds/etc.
Lo and behold, a user named “puzsol” had encountered this very problem, and had traced it to SQL Server’s Service Pack 1, released some time back. And he had a fix!
The answer: turn “Allow remote connections” off. Then turn it back on. Stop SQL Server, then restart it. Ta Da! The problem was fixed!!

Step by step

For those having this problem:
  • Open SQL Server Management Studio on the database server
  • Right-click on the server’s name at the top of the Object Explorer (usually on the left-hand side)
  • Left-click Properties
  • At the top left of the dialog that comes up, under Select a page, left-click Connections
  • Approximately half-way down is Remote server connections, and under that is a checkbox labeled Allow remote connections to this server:
Allow_Remote_connections(click on image for larger size)
If this checkbox is not checked, simply check it and you’re done. You may need to re-start SQL Server. If it is checked, un-check it, then click the OK button. Re-open the dialog, using the above steps, check the box again, and click OK again.
At this point, simply stop, then start SQL Server. This can be done from the SQL Server icon in the notification area, aka the system tray.

Final thoughts

As pleased as I am with the simple solution to this, I have to wonder how this got past Microsoft’s testing. It’s an interesting question how this bug occurs; I suppose somewhere in the binding of the SQL Server settings to the backing setting storage, presumably the Registry, something is broken. Or perhaps the SP1 installation corrupts the Registry under certain circumstances?
Advertisements
  1. #1 by Del on July 11, 2013 - 11:41 am

    This works great!!! Thank you. So simple. Unlike Microsoft’s solutions that DO NOT work.

  2. #3 by John on December 20, 2012 - 12:41 am

    I didn’t get what happens exactly when we does above process and How this problem gets resolved

  3. #4 by fire on July 23, 2012 - 8:51 pm

    I’m cautiously optimistic that this has worked for me, after experiencing the same issue with SP2. It did not work after a service restart however, I think it needed the full reboot. I’m still waiting to see if this issue returns, but so far so good.

  4. #5 by sonu on April 24, 2012 - 3:48 am

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

  5. #6 by andrewcushen on August 17, 2011 - 8:44 pm

    Glad it helped.

  6. #7 by Ozren Sirola on August 16, 2011 - 9:11 am

    Sorry I now read how you found solution in your post. Interesting. Why Microsoft didn’t write something about this problem.

  7. #8 by Ozren Sirola on August 16, 2011 - 9:09 am

    Can you please write how did you find solution ?

  8. #9 by Ozren Sirola on August 16, 2011 - 9:07 am

    Thank you very much … It works !! Our SQL Server 2008 SP2 have some table corrupted and we got “Timeout expired” error but only for some users. We try to solve problem for a days but nothing worked. And today I try your “Step by step” and finally there is no Timeout error and everything works fine. My question is why Microsoft doesn’t write like you instead of check firefall, check database computer name. So your solution is a FIX !

    • #10 by sonu on April 24, 2012 - 3:49 am

      But my problem is not fixed. still same issue occur.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: