I am thinking I know the basics of the problem here, but I want to make sure I know the details before trying to explain it to others.
We recently upgraded our third party application software to a new version. This version completely switched the Java Application Stack to a different vendor. After the upgrade we were told by the vendor that other customers who had upgraded were experiencing tempdb growth. I checked my monitoring and found we are beginning to experience the same.
The vendor claimed the only way to clear Tempdb was to reboot the SQL Server. They also don't really know what is happening because they are only looking at the disk space through the database properties, which I assume uses the flawed sp_spaceused sproc.
My monitoring uses the tempdb.sys.dm_db_file_space_usage dm so I am able to get a much more accurate view. I can see the internal object usage constantly climbing and not going back down at all.
I set up additional monitoring by using sys.dm_db_task_space_usage and can see the connections for this software have large allocation number with very little deallocation. It also looks like it opens connections and keeps them open but they will often be sleeping, which is consistent with the behavior of an application pool, from what I understand.
It seems to me when the requests are ending the code stops but the connection stays open and the internal objects are not being deallocated. Can someone confirm this and help me to explain it? I know SQL but have very little knowledge of connection pooling.
Can this be corrected by adding a connection setting to their pools, something like SET XACT_ABORT = ON?