I have a very limited understanding of how SQL connections work, but based on observation of the sysprocesses table I figure it's a safe assumption that SQL opens/closes connections on-demand depending on the frequency of requests made over the connection.Here's a common scenario that I think I'm seeing; I open a remote application, it authenticates and connects to SQL, makes a few requests and then sits idle for 1 minute (but is still open on the remote client)... on the SQL side of things, SQL appears to close the connection after ~30 seconds until another request is made at which point it will rapidly re-open.

What I'm curious about is if SQL keeps a record of applications that have gone idle... such that I can view a list of 'pending' connections if you like.

but idle applications disappear from the list when SQL closes the connection. It may be that a closed connection is simply that, but I wouldn't have thought a client application would have to authenticate every request it makes? If anyone can provide some insight here it would be much appreciated.

SQL doesn't open/close connections on demand. The client application determines when to open or close a connection. SQL just connects/disconnects the connection based upon the command the application sent.

If you are seeing the connection get disconnected from the SQL Server side, then it's because the client application sent a disconnect or the network dropped it.

Thanks Tara... do you know if there is anything to distinguish an 'application is idle' disconnect from a 'application is closed' disconnect or are both the same?If they're both the same, that would mean every 'reconnect' would have to be authenticated (rather than just the session) - maybe that's valid but it seems like that would introduce unnecessary overhead.

Tara, I'm not really too concerned with the technical details of connections in SQL... What I want to know is if there's any way to identify sessions that are active in SQL?The query I OP'd identifies active connections but when my application temporarily drops the connection it ceases to appear in the resultset... what I want is the resultset that shows the application hasn't closed the entire session.If that makes any sense at all...

Tara, I'm not really too concerned with the technical details of connections in SQL... What I want to know is if there's any way to identify sessions that are active in SQL?The query I OP'd identifies active connections but when my application temporarily drops the connection it ceases to appear in the resultset... what I want is the resultset that shows the application hasn't closed the entire session.If that makes any sense at all...

OK... I re-read your post and got a slightly different understanding. If I'm understanding it correctly, the answer to what I'm asking is "there isn't"... if an idle application disconnects, there is no data in SQL to identify it as idle - rather it's gone until it reconnects... sheesh - not sure why I'm having trouble grabbing this concept ;-/