Re: SQL-08S01 error in FreeTDS/Azure when executing stored proc for a few minutes from Linux (but not from Windows)

On Fri, 23 Nov 2012 18:34:34 +0000
Frediano Ziglio <[email protected]> wrote:

> > Do you have a reference for Microsoft's keepalive requirements?
> >
> > I remember we had a problem with a long-running idle watchdog
> > process that would occasionally end with "read from SQL Server
> > failed".  I never determined why.
> >
> 
> Yes, have a look at
> http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546852.aspx

Thank you.  That description is at least concrete in what they do.  As
for why they do it, it's a vague as anything else I've seen.  The link
provided in that blog post 

	http://support.microsoft.com/kb/137983/?sd=RMVP&fr=1

says 

	"If the connection processes are not cleared up for any reason,
they become "orphaned" or "ghost" processes. These processes may use up
valuable resources such as locks and user connections. "

I have just one quibble: there isn't any reason.  

A process holding locks is resumably executing a query and will in time
respond to the client.  If the client has disconnected at that time,
the server will receive ECONNRESET, "Connection reset by peer" when it
sends the DONE packet.  

A process not holding locks -- an idle connection -- should use very
few resources on a TDS server, measured in kilobytes of memory.  On the
client side we devote less than 10 KB to a connection; surely the
server has little reason to hold much more.  Idle connections have good
reason to exist.  Setting up the server to disconnect them does more
harm than good because for most needs it creates administrative work
disproportionate to the spared resources.  (Bear in mind that most
disconnections, including an application crash, are detected
through normal TCP/IP mechanisms.) 

In the instant case, Azure is executing a query.  Instead of finishing
the job and sending the DONE packet and letting tcp/ip tell it if the
client has disconnected, Azure periodically checks via keep-alive and
aborts the connection.  Transient failures, the very thing tcp/ip was
designed to cope with, thus lead to unusable sessions.  Only the
innocent are surprised the vendor would do that without documenting it
and without using available end-to-end TDS messages instead.  

Richard Stevens correctly observed that "keep alive" is misnamed.
It's more properly called "make dead" because it never keeps a
connection alive; it can only kill one.    

--jkl