Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to decide whether this performance counter is worthwhile monitoring: \\SERVER\MSSQL:Transactions\Longest Transaction Running Time.

So during testing I tried to create some long running transactions but I can't seem to get this counter to read anything greater than zero.

But I can't find a transaction or query that will do it. As a starting point, I used the example query (an insert in a transaction) in the help for DBCC OPENTRAN with no luck.

Is there a query that affects this counter?

share|improve this question
Here's SQL Server's @@VERSION: -------- Microsoft SQL Server 2008 (RTM) - 10.0.1763.0 (Intel X86) Sep 18 2008 21:13:29 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) – Michael J Swart Jul 29 '10 at 13:34
If you have reasons to believe the counter is incorect, report it at – Remus Rusanu Jul 31 '10 at 15:50
I've had varied results when using connect and I've given up for small issues. See and and – Michael J Swart Aug 3 '10 at 17:15
up vote 1 down vote accepted

Little late to the party. I believe the documentation has been updated to explain the answer to this in the last six years, but per my testing it isn't completely correct.

From Books Online: "The length of time (in seconds) since the start of the transaction that has been active longer than any other current transaction. This counter only shows activity when the database is under read committed snapshot isolation level. It does not log any activity if the database is in any other isolation level."

Per my testing, this is not quite correct. If RCSI is not enabled, but you are allowing snapshot, it will show the longest running transaction time for SNAPSHOT queries.

The other tricky thing is that the counter only updates every 60 seconds.

Basically, it's very useful for monitoring if something's going to block version store cleanup when you're using snapshot or RCSI.

share|improve this answer

This counter seems to be a little bit slow/ hit and miss on updating, and I'm not entirely convinced its doing exactly what its explanation suggests, however I seemed to get the most success by doing a select into from (a large table) in an open ended transaction.

Funnily enough issuing a rollback seemed to cause the counter stat to be updated immediately, commit was less effective and sometimes it would update without either. As I say I dont think this counter is exactly what we think, and might just be an approximation/ average.

Regards, Mark @retracement

share|improve this answer
Thanks Mark... sounds like it's not a counter to be trusted. – Michael J Swart Jul 29 '10 at 17:34

This should give you a value in that counter. You'll obviously have to manually stop it.

SELECT '1' AS field1 INTO #tmp
    UPDATE #tmp SET field1=1
share|improve this answer
No luck :-( You mentioned that it "should" give a value in that counter. Have you seen it on your own machine? – Michael J Swart Jul 29 '10 at 13:53
@Michael: yep, tested it before I posted to make sure that I saw something. – squillman Jul 29 '10 at 17:49
This also does not work for me on SQL Server 2005 Enterprise on Windows Server 2003 Enterprise. – John Sansom Jul 30 '10 at 8:20
I got to see something on another server, but not consistently! Which is more weight to the conclusion not to trust the counter :-( squillman, thanks for your help. – Michael J Swart Jul 30 '10 at 18:35

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.