Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 connect.microsoft.com –  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 michaeljswart.com/?p=8 and michaeljswart.com/?p=13 and connect.microsoft.com/SQLServer/feedback/details/349116 –  Michael J Swart Aug 3 '10 at 17:15

2 Answers 2

up vote 2 down vote accepted

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
BEGIN TRANSACTION
WHILE 1=1
BEGIN
    UPDATE #tmp SET field1=1
END
COMMIT TRANSACTION
DROP TABLE #tmp
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

 
discard

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.