July 26, 2017 at 2:35 am
I have an SQL 2016 instance for a vendor database. In some of the environments (all on separate instances and servers) I notice the tempdb has a very large version store and is growing slowly. The database has snapshot isolation and read committed snapshot on. I capture all the sessions that have an entry with the dmv sys.dm_tran_active_snapshot_database_transactions and I get a 5 sessions all of which have open_tran=1 that have been running for many days all coming from the application. All of those 5 sessions have the sql text as FETCH API_CURSORxxxxxxxx calls. I then check sys.dm_exec_cursors for those cursor definitions. To my confusion however, only 2 rows are returned and not 5. How can there be 5 sessions with an open transaction and FETCH API_CURSORxxxxxxxx as the sql text but only 2 cursors open? Am I missing something?
I have raised a support call with the vendor and a restart of the application clears the version store issue (until it builds up again) but I am more curious about only 2 results being returned in sys.dm_exec_cursors
Thanks
July 27, 2017 at 11:09 pm
Anyone have an idea on this one? Could it be that the cursor was closed but not deallocated?
July 28, 2017 at 6:43 am
Yeah, I also need to know, maube someone knows???
August 3, 2017 at 12:32 am
OK I think I have solved this one myself... to a degree at least. I turned on a sql trace and started up the application. I then looked at sys.dm_tran_active_snapshot_database_transactions to see if there were open transactions which there were. I grabbed the transaction id and used that with the session id to search my trace data. I noticed that the session has issued set implicit_transactions on and then proceeded to run a cursor which simply selects data. The cursor is closed off but there doesnt appear to be any commit or rollback transaction issued. When the select statement is issued, this opens the transaction. That is what appears to have opened the transaction and remains open.
Time to talk to the vendor
August 3, 2017 at 12:38 am
Is there a way to list all the sessions and what implicit_transactions is set to for each one? I cannot see it in sys.dm_exec_sessions
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply