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. enter image description here

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. enter image description here

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. enter image description here

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?

  • What do you mean by "Java Application Stack"? J2EE container of some sort? Check with the application administrator if the connection pool is set to close idle connections after some time; a poorly written application can indeed keep resources, such as cursors, open unnecessarily. – mustaccio Jul 31 '15 at 18:24
  • Right, the java provider went from jboss to TomcatEE. I don't believe the connections have to be idle for this to occur, I think they can be reused but the previous connection's resources are still not being released. – Dave.Gugg Jul 31 '15 at 18:26
  • They are indeed reused -- that's what the connection pool is for. However, if the application does not care about releasing resources, you could use the connection pool configuration to work around that deficiency by forcefully closing connections that may be hogging resources. I'm not sufficiently familiar with Tomcat to tell you exactly how to do that. – mustaccio Jul 31 '15 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.