Does SQL 2005 automatically kill idle connections after some time?

[Dave Herrmann]

We are currently seeing a problem that we did not see prior to the install of
Service Pack 2 and the latest patches.

We have a client application that users run to connect to our SQL server
over the network. The users typically start this application in the morning
and leave it running all day. This application establishes a connection to
the SQL server at start up and leaves that connection open until the user
closes down the application.

During the day, the users may perform some SQL operation and then return to
an idle state. Then later they perform some other SQL operation and then
return to an idle state. All of these operations are done through the same
initial SQL connection. So that connection may be idle for quite a while
before the user wants to use it again.

We are now seeing that these clients are not able to initiate a SQL
operation after being idle for some period of time. It appears to be about an
hour or so. This means that the user must close out their client application
and restart it and then login again.

My question is this: does SQL Server close out idle connections
automatically? And if so, is there a configuration setting we can use to
increase this idle timeout.

Thank you.

-Dave

[Dave Herrmann]

One thing I forgot to mention is that the client apps being used are not
.NET. They are C++ MFC apps that connect to the SQL server through a
middle-ware RPC Server. The RPC Server makes the initial connection to the
SQL Server on behalf of each client and passes the connection handle back to
the client. The client passes in this connection handle to the RPC server
each time a SQL operation is initiated by the client.

Also, each client has it's own individual connection using the credentials
of the user running that client app.

-Dave

[Dave Herrmann]

Ok, this may not be something the SQL Server is doing. We are still using
DB-Library on the RPC server to make connections into the SQL Server (I know
this is EOL'd but it was still working). When this error occurs, the actual
SQL connection is still there according to sp_who2, but it appears that the
DB-Library has timed out our DBPROCESS handle so the next time the client
makes a request to the RPC Server, the server tries to use the DBPROCESS
handle and it no longer works. We haven't changed DB-Library for years so I
don't know why this only now starting to happen.

If anyone has any ideas, I'm all ears.

-Dave

[Erland Sommarskog]

So much is clear, SQL Server does not kill idle connections by itself.

Nor do I recall that DB-Library has a similar feature.

I would guess that this is due to a network glitch, although in this case
I would not expect the connection to still be alive on the SQL Server
side.

What error message do you get when you try to reuse the connection.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx