Uncommitted implicit transactions

Question

Uncommitted implicit transactions

YaHozna

Ten Centuries

Points: 1022
More actions
April 20, 2010 at 11:03 am

#221553

Bit puzzled by this. I've got a database that's started to suffer from a lot of blocked processes all of a sudden where resources locked by implicit transactions through SELECT statements don't seem to being released.
In looking into it there seem to be a number of uncommitted implicit transactions for the database in question. What I'm puzzled by is that DBCC OPENTRAN doesn't report any but if I query sys.dm_tran_active_transactions I'm seeing several implicit transactions, some dating back about a week. Does DBCC OPENTRAN only report explicit transactions?
Regards,
YaHozna.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

john.arnott SSChampion Points: 11882 More actions · Answer 1

My understanding is that DBCC OpenTran looks at the transaction log for the oldest transaction not yet committed. If there's no change to the db, then nothing is written to the log, which would mean OpenTran won't report a incomplete Select if that's the only thing in a transaction, implicit or explicit.

Caveat: I'm a developer, not a DBA, so I hope for someone with greater knowledge to confirm or dispute that statement.

YaHozna Ten Centuries Points: 1022 More actions · Answer 2

Thanks for the feedback, John. Are SELECT statements actually logged though, as they are not required for any recovery? That would perhaps explain why they show up in sys.dm_tran_active_transactions but not DBCC OPENTRAN, if DBCC OPENTRAN is only looking for 'recoverable' transactions - e.g. UPDATE, DELETE, etc.

Regards,

YaHozna.

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 3

Select statements don't change any data so they aren't logged.