How to identify if a specific connection has any activity

[MCMartin]

I know about the Last Request Type which can be used to see what a connection have done, but how to identify if an open connection is still showing any activity or if it is idle.

[VolkerBarth]

What about "LastReqTime" and "UncommitOps" in sa_conn_info's output?

[VolkerBarth · Accepted solution]

You might try the following (using the ReqStatus connection property😞

select *, connection_property('ReqStatus', sci.Number) as ReqStatus
from sa_conn_info() sci
order by 1;

[Breck_Carter]

The following excerpt from the Foxhound Help for the purge process describes how Foxhound determines what connection-level data can be deleted because it is "uninteresting"; this goes beyond "idle" but it may be of interest to you:

A connection is defined as "uninteresting" when all the following conditions are true for the previous and current samples recorded by the Foxhound Monitor for that connection:

• the two connection-level samples really are for the same connection (the LoginTime property remains the same then and now, proving that the connection number hasn't been reused),

• the connection was not blocked and did not hold any locks then or now (BlockedOn and LockCount = 0),

• no new request has been started for this connection (LastReqTime and LastStatement were the same then and now),

• the server wasn't busy servicing this connection (ReqStatus is empty or 'Idle' then and now, and the ApproximateCPUTime, Commit and Rlbk properties have not changed then or now), and

• the connection hasn't done any real work (RollbackLogPages = 0 then and now).

[MCMartin]

Very sophisticated! I really would like to accept yours and Volkers answer. Anyway as only one can be choosen I will take Volkers because it is the more straight forward and fits more what I had in mind 😉