Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

You would only be able to change this for the whole server/instance not just for your session. Do you really want to change that for every connection? The default setting I believe is 10 minutes for remote query access.
–
Shawn MeltonJan 26 '12 at 20:07

1

Are you by any chance running into a nightly job that's doing a ALTER DATABASE <BLAH> SET SINGLE_USER WITH ROLLBACK IMMEDIATE or something like that? Usually, something has to happen on the server for SSMS to force another actual login.
–
BrandonJan 26 '12 at 20:32

@Brandon The only thing that's changed is I am on a new PC with a freshly installed SQL 2008 R2 set of tools. So I was assuming I missed a setting that I had previously set on my old PC which did not exhibit this timing out issue. Nothing on the server side has changed.
–
Mason G. ZhwitiJan 26 '12 at 23:18

4

New PC power settings? Like the PC (or network card) is going to sleep and dropping the network stack or something like that maybe? AFAIK, there's nothing in SSMS that controls this, or at least, nothing I've ever set anyways. :)
–
BrandonJan 26 '12 at 23:44

1 Answer
1

Connection is broken between your PC and SQL Server (for example some Switch can be powered-down, re-started etc)

Connection can be closed from SQL-Server side if your admins make some kind of maintenance and simply kill connections, or if your Server is running in Virtualized envronment, it's instance could be "paused" to make a snapshot.

list of reasons can be really big

you can start analysing using simple tool: when you go home, just run:

ping -t NAMEOFYOURSERVER >c:\temp\log.txt

this will start permanent ping and when you will come next day - just Ctrl-Break and look into the file if there are Time-Outs. This way you will know if at least IP connection was alive.

I don't think this is the reason, unless it's a setting within this PC, because the PC I'm on now is connected to the same exact ethernet, switches, firewalls, etc as the last system I was using which this one replaces. So what I'm trying to figure out is what setting in this PC might be causing it to disconnect each night. I am going to explore whether this machine is sleeping or hibernating in a different way than the old system. Thank you for the reply though, I am rating it up because ultimately I do think this can help someone.
–
Mason G. ZhwitiMar 16 '12 at 22:06

You can even use the same technic: run continous ping of YOUR PC from some other PC and you will discover, if it is loosing network connectivity.
–
Andrei RantsevichMar 19 '12 at 3:54