I already went into the Sql Server Configuration manager and set the TCPIP Keep Alive value to 0 (default setting 30000). But still, after like 5 minutes it disconnects, and I have to re-log back in. The problem with this is that I will usually have several #temp tables open -- then I have to re-create/populate all these #temp tables.  I'm running win7 SP1, Sql Server 2008 R2 Developer -- SP2. Is there something I can set in the registry maybe? What can I do to prevent sql server 2008 R2 Developer from disconnecting?

Do have SQL Server running on your local machine or on a remote machine? In the latter case, I would recommend that you replace that broken network cable of yours.

That is, SSMS does not disconnect you every once in a while just for fun, but this is typically due to some flaky network connection.

Do have SQL Server running on your local machine or on a remote machine? In the latter case, I would recommend that you replace that broken network cable of yours.

Do have SQL Server running on your local machine or on a remote machine? In the latter case, I would recommend that you replace that broken network cable of yours.

No, this is running on my local machine.  It is very annoying.  I was running Sql server 2008 R2 locally on another win7 machine for a couple of years and did not have this problem.  Was it maybe some update?  I have seen that I am not the only experiencing this problem.  But I can't seem to find the

That is not supposed to happen.

We would need more information about this disconnects. How do notice them? Do you get an error message at some point?

What does this query return?

SELECT connectionproperty('Net_transport')

I seem to recall that SSMS 2008 R2 has some irritating quirks; I have mainly stayed away from it. Maybe this is something that strikes? You could download SSMS 2012 from
http://www.microsoft.com/en-us/download/details.aspx?id=29062
(When you click download you get a list of options, select the one with Management Studio.)

And why SSMS 2012 and not SSMS 2014? Because SSMS 2014 has some new (very) irritating quirks.

I agree with Erland there can be many reasons. How about Antivirus running on the system. Antivirus has this nature of disconnecting sessions. There are lot of options provided check which applied in your case.

Thank you all for replies.  I will try to reiterate the situation -- Sql Server 2008 R2 -- I have SSMS up and running -- I create various #temp tables for testing out various procedures.  I walk away from my computer (win7 sp2) for a while.  I come back -- the computer is in sleep mode.  I log back in and SSMS appears to be still running.  I try to run a query from SSMS (formerly Query Analyzer Window).  The server login screen comes up.  I login to the sql server and it defaults to Master DB.  I have type in use xxxDB.  Now I have to recreate and reload all my #temp tables.  This is very annoying.  I did not have this problem on another win7 workstation.   Sometimes the sql server logs off after only a few minutes.  I'll have SSMS running, then I go to Visual Studio to work on a front end application (with Entity Framework).  The app can access the sql server fine.  But when I go back to SSMS to see if a table did get populated from my app -- the login screen comes on and then I have to tell it to use xxxDB.  It's kind of random, but it is an ongoing situation.

>>Check if you have Auto-close option enabled for the DB, if so, disable it.<<

Where do I check this?  Is this in the sql server options?

I walk away from my computer (win7 sp2) for a while.  I come back -- the computer is in sleep mode.  

There's your answer, your computer is going in sleep mode. Most probably your hard disk are being shut off too. Go to Control Panel --> Hardware --> Power Options --> Make sure high performance is selected --> Change When Computer Sleeps --> Change Advanced Power Settings --> Expand Hard Disk Node from the tree view --> Select "Never" for turn off hard disks after option

I walk away from my computer (win7 sp2) for a while.  I come back -- the computer is in sleep mode.  

There's your answer, your computer is going in sleep mode. Most probably your hard disk are being shut off too. Go to Control Panel --> Hardware --> Power Options --> Make sure high performance is selected --> Change When Computer Sleeps --> Change Advanced Power Settings --> Expand Hard Disk Node from the tree view --> Select "Never" for turn off hard disks after option

Rich P123, I did ask you about the Power option in my earlier thread. Because, I had the same issue sometimes back, And when I changed the options, the issue did not re-occur. So, you may try the same and let us know how it works for you.

Go to Control Panel --> Hardware --> Power Options --> Make sure high performance is selected --> Change When Computer Sleeps --> Change Advanced Power Settings --> Expand Hard Disk Node from the tree view --> Select "Never" for turn off hard disks after option

The hard disks should have nothing to do with it. I just checked my machine, and I have set the hard disks to be turned off after 20 minutes, and I never have the problem that Rich describes. True, I am not running SSMS 2008 R2, but the hard disks have nothing to due with the communication between SSMS and SQL Server. I do have the Sleep setting set to Never.

I think that as long as Rich works on the machine, the connection to SQL Server should stay alive.

I think it is worth checking the SQL Server errorlog when this happens. Is there a dump or some other message indicating that the connection has been lost? Also, check when SQL Server started - maybe something/someone restarts SQL Server? Furthermore, check the autoclose setting for the database.

The autoclose property of the database is already set to false. Here are some images of my system (which is a private system -- my own workstation -- no one else touches it -- well, theoretically -- at least not physically). In the first image I am logged into the server and working in  EF1_DB.  I leave it for a while and notice by the New Query window -- it is empty after a few minutes!  When I try to run USE EF1_DB -- the login screen comes on (note:  this project is currently committed to sql server 2008 R2 because that is also the production server).

fig1 -- logged in to EF1_DB

fig2 -- Window underneath New Query is Emtpy after a little while - why?  I was only away from it for a few mintues

fig3 -- when I try to run USE EF1_DB -- the login screen comes on -- what is happening here?

I all query tabs I can see "not connected", also in your current one, that's why you get the login dialog. One reason could the the already mentioned power safe Options of Windows, the other one is this "Query Option" setting to automatically disconnect after query execution; is this Option set?

As Olaf says, in your screen shot, all tabs are "not connected" which would happen if the computer has taken a nap. Now, maybe you after a nap connected one window, then that window is connected. But the rest are not. So if you come back to SSMS after some work in VS to work another tab, you will need to connect that tab as well.

May be the below: (You may try)

Control Panel->Power Option->(Balanced/Power Saver)->Put the computer to sleep -> Never.

• Edited by Latheesh NKMVP Monday, December 29, 2014 9:42 AM

I wish to continue with thanks to all for replies.  The disconnect property after Query execution is not checked.  And I am not sure about not letting my computer sleep.  Sometimes I have to leave it for extended periods. 

One thing I did notice is that when I do connect -- the database window shows my server name and an IP address instead of LocalHost.  Could that be of some issue?  How can I make the server connect to LocalHost instead of an IP address?  I don't even recognize that IP address.  I see 10.50.4033 -- and that is not in my IPconfig list.  I don't need to make this server public -- it is just for development (2008 R2 Developer).  How to make LocalHost?

10.50.4033 is not an IP address; it looks like a version number for SQL 2008 R2. An IP address has four fields, and all numbers are in the range 0 to 255.

By the way, reviewing the thread, I can't see that you ever posted what output you get from this query:'

SELECT connectionproperty('Net_transport')

By the way, reviewing the thread, I can't see that you ever posted what output you get from this query:'

SELECT connectionproperty('Net_transport')

"Shared memory" is the expected in this case.

I think that with what we have seen so far, the conclusion we outsiders can draw is that this is an issue with your computer going to sleep, and if you don't like that turn off sleep, at least as long as you are not on batteries. And don't close the lid.

Check if you have Auto-close option enabled for the DB, if so, d

I wish to continue with thanks to all for replies.  The disconnect property after Query execution is not checked. And I am not sure about not letting my computer sleep.  Sometimes I have to leave it for extended periods. 

Good day Rich P123,

Sometimes for monitoring we have to do actions that are one-time in order to check our assumptions. In this case I recommend that you do this check :-)
you just need to check it once (like 24 hours), to make sure that this is the the source of the issue, or not :-)
In any case you can use screensaver, but make sure that you do not close disks as well.

Please inform us your

Power management default is it will go sleep mode after 5 minutes if system is idle, this needs to be changed.

Thanks to see that issue is resolved.

Hi Rich P123

Please remember to close the thread by marking the answer/s. If you do not find any answer and you wrote the summarize solution, then you can mark your own response as well (you do not get points for this). In any case you should vote for response that help you or was part of the solution (it cost you nothing and those who wrote the responses gets 5 points for each voting... you can not do anything with the points... but it is nice).

I glad to see that issue is resolved :-)