First off SQL check is a great little tool , we use it on our monitoring screens as it gives awesome visual feedback straight away.
However after pointing it at a SQL server which sits on VMWARE there are a lot of balls with a “Connection Idling in Transaction” status. But this is not the case there are no open transactions why is SQL Check reporting this?
When i run DBCC OPENTRAN it only brings back one process at a given time but Idera SQl Check is showing me 50+ idling transaction balls?
I use SQL Check on other SQl servers not in a virtual environment and they seem to be fine?
We’re basing this indicator on the “open_tran” column in sysprocesses, which may return a transaction in a few situations where DBCC OPENTRAN does not. My research on the difference between the two values suggests that in most cases the “missing” transactions in the DBCC operation correspond to some specific cases where SQL Server is smart enough to know that a particular query isn’t really making a data change that would be considered a transaction. Still, this difference in open_tran does sometimes appear to allow you to turn up “ghost” transactions that have been keeping a transaction live (and uncommitted) where DBCC OPENTRAN would not.
Thanks for that, there is indeed open_trans set to 1.
These do seem like ghost trans as majority are sleeping and not blocking anything.