For thousands of years theologians have offered various thoughts on the question of what happens when we die. Does some part of who we are come back to start over again? Do we go to some other place? Is “the end” simply that: the end, in an absolute sense? When it comes to Sessions in SQL Server and their “afterlife” (i.e. when the Connection is closed), these same theologians have had very little to say. This might be due, to a small degree at least, to most of them being long dead prior to the invention of computers. And, perhaps most of the rest of them not knowing of SQL Server has something to do with it. But, the ones that both have been alive during these modern times and know of SQL Server? I suppose having more “important” things to ponder, and not caring about SQL Server in general is somehow an acceptable excuse explanation for dropping the ball here. But some of us do care about this stuff. So I, like several others before me, will take on at least some of what those lazy, irresponsible theologians have neglected to do, and write about what happens when a Session dies (or, when we think it dies).

Sessions, in SQL Server, are born when a Connection is made from a client library to SQL Server. Temporary objects – Tables and/or Stored Procedures (yes, these are a thing) – may be created during a Session’s lifetime. The question is: for those temporary objects that are not explicitly dropped, what exactly happens to them? It is commonly known that they magically (ok fine, “automagically” — ok, ok, FINE, “automatically”) get dropped. But when do they get dropped? When the Session ends, right? And the Session ends when the Connection is closed, right? Well, that is certainly the common / conventional wisdom, at least. But is that understanding of the nature of Sessions and temporary objects correct?

And the Answer is…

The answer to this very important question is pretty much the same as it is for most of these types of questions: IT DEPENDS And what it depends on are the following factors:

1. For global temporary objects, is an active Transaction from another Session still referencing the object?
2. Is Connection Pooling being used?
3. Is the Connection enlisted in a Distributed Transaction that is still active?

Occasionally someone will mention factor #1. It is pretty rare to see factor #2 mentioned. And I don’t recall ever seeing anyone mention factor #3. But, in all three cases, the temporary object does survive beyond the Connection being closed. Why don’t we take a closer look at each of these factors, individually and some in combination, starting with the scenario of not having any of them being involved. And yes, I have verified all of the information below using a test application that I built specifically to research this behavior.

No References to Global Temp Objects, No Connection Pooling, and No Distributed Transactions

This is the simplest, most basic scenario, which behaves just as most people expect with regards to when temporary objects are dropped. When the client Connection is closed, the Session ends and all of the temporary objects created from within this Session are dropped. There’s absolutely nothing surprising or noteworthy going on here.

Reference to Global Temp Objects From Other Sessions, but no Connection Pooling, and No Distributed Transactions

When Transactions (including single statement queries that aren’t in an explicit Transaction) from other Sessions reference a global temporary object, that object cannot be dropped. And, if the object cannot be dropped, then neither the Session, nor the Connection that initiated that Session, can be dropped. If the client program closes the Connection, then local temporary objects are dropped immediately. But global temporary objects (along with the Session and the Connection that they exist in) will continue to exist until the last reference completes.

Connection Pooling, but no References to Global Temp Objects From Other Sessions, and No Distributed Transactions

Here’s where it starts to get fun. Client connections that have Connection Pooling enabled do not end the Session or the actual Connection when the connection is closed (or even disposed) on the client side. This is, after all, the purpose of Connection Pooling: save the time is takes to authenticate and initialize the Session when opening and closing many connections by leaving them open.

Many people assume, incorrectly, that any “cleanup” steps would occur upon the client closing the connection. But this is not what happens, and in fact, SQL Server might not even be aware that the connection was closed on the client side. For SQL Server, it just looks like the client isn’t sending any commands.

At some point either:

• the connection in the Connection Pool times out and fully closes, or
• the Connection Pool is reset and all connections are fully closed, or
• the client app terminates and the Connection Pool dies, fully closing all of its connections, OR
• the client app opens the connection again and executes a command (any T-SQL batch), at which point the Session is “reset” to have the appearance of being newly started.

During the time between the client app closing the connection on its side, and opening it again and running the first command, nothing happens to the Session. This means that orphaned Transactions remain active, and temporary objects, both local and global, continue to exist. And, because global temporary objects still exist, they can still be accessed (we will come back to this fun fact in a moment).

When the client app opens the same Connection again, initially nothing happens with the Session. It’s not until the client app executes the first query batch (e.g. SqlCommand in .NET) that the magic happens. Just prior to executing any T-SQL in that first query batch, an internal Stored Procedure — sp_reset_connection — is executed which cleans up anything left-over from the previous executions using this Session / Connection so that it will be “just like new”. There are quite a few things that sp_reset_connection cleans up, and a future post will be devoted specifically to Connection Pooling and sp_reset_connection, but for now we are just concerned with the “dropping all temporary objects” task. It is at that moment when temporary objects are automatically cleaned up.

On systems with a decent amount of activity where the connections in the Pool get recycled fairly quickly, this behavior will likely be of little consequence. But, this timing of events can be quite important for systems where there is a bit of time between when the connection is closed on the client side and when the first batch is executed after re-opening the connection. Consider that:

• Temporary Stored Procedures will still exist, and could possibly have an open Transaction that is now orphaned, but won’t be aborted until the first batch is executed upon the connection being re-opened
• Temporary Tables created either as global objects or via an ad hoc query (i.e. not within a Stored Procedure) will still exist, and could possibly contain a large amount of data. That tempdb space won’t be freed up until the first batch is executed upon the connection being re-opened
• Global temporary objects not only still exist, but can still be accessed by all other Sessions. This is probably less of an issue for Global Temporary Stored Procedures, but Global Temporary Tables are more likely (I would think) to encounter the following scenario…

Connection Pooling and References to Global Temp Objects From Other Sessions, but No Distributed Transactions

Because Connection Pooling keeps the Connection open and the Session running (likely “sleeping”), and because global temporary objects can be accessed by all Sessions, there is nothing to stop any other Session from locking a Global Temporary Table prior to sp_reset_connection dropping it. When Transactions (including single statement queries that aren’t in an explicit Transaction) from other Sessions reference a global temporary object, that object cannot be dropped.

So far, this scenario is no different than when no Connection Pooling is involved. But, when Connection Pooling is involved, it will attempt to reuse this Session. And, since the Session still has settings and objects left over from before the Connection was closed, sp_reset_connection is executed to clean everything up. One of the clean up tasks is to drop all temporary objects.

First, sp_reset_connection will drop any local temporary objects. This should never be a problem as those cannot be referenced by any other Session. Next, sp_reset_connection will attempt to drop any global temporary objects. This usually succeeds, except when one (or more) of the global temporary objects is referenced / locked by another Session. When that happens, sp_reset_connection cannot drop those objects. If the locks are released prior to the Command Timeout (.NET default is 30 seconds), then the DROP statements can complete and everything should be fine. But, if a global temporary object is still locked when the Command Timeout expires, then the you get the following error and the connection is fully closed:

Unhandled Exception: System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

—> System.ComponentModel.Win32Exception: The wait operation timed out

At this point, the connection is no longer in the Connection Pool. The Session / Connection still exist in SQL Server, but the client app cannot re-connect to that Session. Once all of the locks are released, the global temporary objects will be dropped, and the Session and the Connection that they exist in will end.

To be very clear about this scenario: the longer the amount of time between when (a) the connection is closed on the client side and when (b) the first batch is executed after re-opening the connection, the greater the chance of running into this scenario. But, this scenario can still occur even if the sequence of events happens within a very short amount of time. It could happen in just 2 milliseconds (ms), or possibly even less time than that. Closed connections being idle in the Connection Pool for longer merely increases the chances of running into this type of problem.

Distributed Transactions

This scenario is a bit more involved / complex than the others, and it is far less frequently encountered, so it will have to wait for the moment. Eventually it will either get its own post, or I will update this one with that info. I will also post the code for the test app I wrote to do this research when I post the info for Distributed Transactions. For now I will just say that within the context of a Distributed Transaction, when sp_reset_connection is executed, it does not attempt to ROLLBACK the open Transaction.

Temporary Object Life Cycle Chart

To help visualize both the timing of events and the difference between pooled and non-pooled connections, I have placed the information detailed above into the following process workflow chart:

What to Do

I generally do not manually drop temporary objects, and instead prefer to have the system automatically clean them up. This is primarily due to reading somewhere that DDL in Stored Procedures / Triggers can cause recompiles. BUT, if there is a large amount of data in Temporary Tables (local that are created via ad hoc batches, or global), or if there are odd Timeout Exceptions happening and I know that global temporary objects are being used, then I will manually DROP those objects.

Conclusion

Any discussion about when temporary objects are cleaned up is incomplete if the effects of Connection Pooling are not discussed. And, considering the performance gain of using Connection Pooling, most applications should be using it (it is enabled by default within .NET) and so everyone should be aware of the behavior detailed above. Especially if tempdb is taking up more space (data space, not log space) than it “should”. And even more especially if the application is encountering those odd Timeout Exceptions.

This behavior is important to understand even if the one main application you deal with doesn’t use Connection Pooling (although, why not use it?) or you are using it but haven’t seen these types of issues. Each application has the choice of using Connection Pooling or not. So, even if the main app doesn’t use it, if there are any other apps connecting (including people in departments that aren’t getting the “support tools” that they have been requesting for years so that they can do their jobs so they resort to using Access or Excel to connect and do Lord knows what), they might be using Connection Pooling.