dcsimg
Results 1 to 7 of 7

Thread: [RESOLVED] Does MS SQL Server have an idle timeout for connections?

  1. #1

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    942

    Resolved [RESOLVED] Does MS SQL Server have an idle timeout for connections?

    I'm developing a class to do simple tasks with SQLConnection. The first one will be rapidly firing INSERT commands so I thought it would be smarter to open the connection in the constructor then close it when I'm done in another method. But there's a situation that occasionally requires user intervention and if the user isn't immediately available it might take several minutes before it takes the corrective action and executes the next command. Will the connection be terminated on the server end after too much idle time in the program? I know with things like FTP connections the server will leave the connection open for a period of time but close it after a set period of inactivity.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,642

    Re: Does MS SQL Server have an idle timeout for connections?

    There's two types of time outs - one is the ConnectionTimeout ... which determines how long it waits while making the connection before it times out...
    The other is the CommandTimeout ... which determines how long it will wait while executing the command before it times out.

    I don't think either of those apply...

    so the short answer is, no there is no idle timeout on the connection. Probably good for two reasons - 1) people will sometimes develop their app to open the connection once and keep it open for hte life of the app... whether the user uses it or not. 2) killing idle connections would kill the purpose of connection pooling, where connections are "closed" but held on until a new request comes.



    What you could do is when these situations happen, go ahead and close the connection... then once the user does what they need to do, re-open the connection and continue on.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,415

    Re: Does MS SQL Server have an idle timeout for connections?

    I've worked in situations where the network had an idle time out on it though. Personally I'd avoid the "open it and keep it" aproach these days because 1) it does leave you open to these kind of issues (amongst many others) and 2) with connection pooling it doesn't give you any real benefit anyway.

    If you're not aware of it, connection pooling is built into .Net and means that, when you close a connection, it's not really closed. Instead it's simply released back into an available pool so that a subsequent open request can simply grab it from the pool instead of having to open a new connection. It means that it's pretty much always better to open and close a connection for a single operation because you're not really opening and closing it so you don't incur the overhead.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    942

    Re: Does MS SQL Server have an idle timeout for connections?

    Techgnome: You're right. I don't know why it didn't occur to me to close and reopen. I'll consider that.

    FunkyDexter: I started to read about connection pooling Friday evening but then there was this glass of wine trust in front of me by a pretty brunette and I lost interest. I thought connection pooling was on the server side however. I'll look into that next. I love .NET but I'm often frustrated on how it's often a 'black box' and our commands and logic are not implemented as they seem and I often end up worrying about things it's actually taking care for for me.

    I have to finish this one class but once I'm done I can do some testing with the connection state. I normally employ a Using statement and from what I'm hearing this might be the wiser way to go. Will that cost much more time? Maybe none given the behavior of connection pooling? I'm doing an insert a few times a second so it's not like I'm firing as fast as I can. I'll start with the Using block for each iteration and do a benchmark then try leaving it open.

  5. #5

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    942

    Re: Does MS SQL Server have an idle timeout for connections?

    I found this article that was very helpful. In the Removing Connections section I read "The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed." This would mean that the overhead I feared shouldn't be an issue. In a way it's already doing what I was hoping to accomplish. Kewl.

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,415

    Re: [RESOLVED] Does MS SQL Server have an idle timeout for connections?

    there was this glass of wine trust in front of me by a pretty brunette and I lost interest
    Yeah, that's do it for me too

    I love .NET but I'm often frustrated on how it's often a 'black box'
    I know what you mean. I'm always struck that when I used to work with VB6, debugging consisted of spending a few minutes working out when I needed to do then an hour or so implementing it. In .Net I spend an hour or so working out what I need to do... then change one line of code to achieve it.

    .Net does blackbox an awful lot of stuff which is great once you know about the black box back can make finding the right box hard.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    942

    Re: [RESOLVED] Does MS SQL Server have an idle timeout for connections?

    And I don't mean to complain. I'm sure there are hundreds of things .NET has fixed for me that I wasn't even aware of. It's probably like to long-snapper phenomenon. Do your job perfectly and save the play dozens of times and no one will notice. Snap the ball poorly once and you're on the jumbo-tron getting booed.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width