SQL Server Management Studio disconnected after a period of inactivity

Question

I got a nice new fresh pc and installed SQL Server Management Studio on it. I have a "problem" that when I am inactive for about 30 minutes that I lose connection to the database servers where I had connected to. The query screen show the messages "not connected".

On my former pc I could be inactive the whole weekend and it was no problem. I can't find a "setting" or something to change this. Does anybody know how I can restore the old situation?

Answer 1

Well this may have some different causes.

First of all, I just guess that - since you mentioned your fresh new PC and the fact that you installed it on your own - means that it's your private PC, right? In this case, I think you probably run a SQL Server Express on your local machine, right?

If the above is correct, I would suggest the following things to check.

1. Did you migrate the Databases from your old system to the new one or did you recreate them on the new system? If you just recreated them, it might be that you accidentally set the AUTO CLOSE of your database. This may cause such a behavior. You can check it in your properties of your Database. Anyway I would suggest to disable it.

You can achieve this using the following code:

ALTER DATABASE [yourDatabase] SET AUTO_CLOSE OFF WITH NO_WAIT

2. It may be, that you have a wrong instance configuration. On SQL Express there is a little exotic instance option called user instance timeout.

You can check it using the following code:

sp_configure 'show advanced options', 1; 
RECONFIGURE; 
GO 
sp_configure 'user instance timeout'

You can configure this property by calling:

sp_configure 'user instance timeout', 1440;
RECONFIGURE;

The above statement will set the timeout to 1 day.

If you are running a real instance on another server and just try to connect to the instance using your new PC, one of the following options may help you:

1. It may be that your new PC has some power settings which will disable your network card after some time of inactivity. This may occur. I had the same option in my network interface. Just check it on your hardware manage and go through your options. In my case (Intel) it was very hidden in a sub-sub-sub-sub-dialogue. It causes a power safe mode after 10 minutes of inactivity. Which was great for the performance on my laptop, but not if I run big queries.

2. It may be a bad DHCP configuration. I had a similar problem in a hotel where my laptop cycled every 10 minutes and get a new IP-Address. This was very harmful on some systems. But I don't know how the SQL Server would handled it, I haven't connected to my companies SQL Server over the Hotel WLAN (of course!). :-D

Hopefully one or two of the options help you to fix your issue.

Answer 2

One of the reasons why SSMS disconnects is related to the power management of the system, which requires to be adjusted from device manager network adapters for both W-LAN and LAN Power Management tab and set allow the computer to turn off this device to save power to unchecked.

Another adjustment to do is to set the connection time-out option to zero which indicates no time-out and set the same for execution time-out if not already set.