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.

asked 25 Jan '12, 06:42

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

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

(25 Jan '12, 07:26) Volker Barth

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;
permanent link

answered 25 Jan '12, 07:36

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

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).

permanent link

answered 25 Jan '12, 08:49

Breck%20Carter's gravatar image

Breck Carter
27.0k424582830
accept rate: 21%

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 ;-)

(25 Jan '12, 12:07) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×124
×12

question asked: 25 Jan '12, 06:42

question was seen: 804 times

last updated: 25 Jan '12, 12:07