the input buffer being
The application connecting to this server uses multiple databases, so _dummy is a blank database which connection pools open connections to; the database context is switched as required.
Hmmmm. Are you sure about that? When the connection is made, assuming that the Connection String keyword "database" (or one of its synonyms) is used (i.e.
database=_dummy; ), then you wouldn't see a
USE [_dummy] statement. No command is executed to set the connecting database if specified as part of the connection. If, however, you use
SqlConnection.ChangeDatabase() after connecting, then you would see the
USE statement. So, either something is not set up correctly and the code truly isn't connecting to
_dummy, but is instead changing to
_dummy upon connecting (not quite the same thing, especially if you are trying to avoid Connection Pool Fragmentation), OR someone or some process is executing that
Also, I'm not sure why, if this is really the application doing this that you would even see the
USE statement. You should be seeing what the
SqlCommand object is executing. If this Session is really part of the application's Connection Pool, then even with the connection being "closed" you should still see the last query batch submitted when executing
DBCC INPUTBUFFER. Of course, the app code could be switching the Database right before closing the Connection, though I don't see why anyone would intentionally do that.
What I thought was happening was that a query was timing out after obtaining locks, not rolling back the transaction and the connection was being released by the application back to the connection pool, which was resetting the database context before problems occurred due to the open transaction.
Assuming that the Transaction was started using
BEGIN TRAN; via a
SqlCommand, then yes, up until the part about "resetting the database context before problems occurred due to the open transaction". This is a common misunderstanding of how Connection Pooling works. The Session is not reset when the Connection is closed. It is reset upon the Connection being opened again, and the first
SqlCommand is executed. Simply opening the Connection is not enough to call
So, it is possible that, if the app code
try / catch / finally logic is such that the Timeout Exception is handled cleanly and the Connection is a) returned to the Pool, and b) not re-used again for several minutes (or however long you are seeing the blocking occur for), then that Transaction and its locks could very well be sitting out there for a bit, until either the connection is re-opened and something is executed, OR the application ends, OR the connection in the Pool times out, OR the Connection Pool is manually cleared.
When I get the time I was going to blog about all of the various behaviors and technicalities related to Connection Pooling. But for now I have documented at least some of it in relation to temporary objects: Sessions, Temporary Objects, and the Afterlife
XACT_ABORT ONto rollback the transaction following an error, including client timeouts. Perhaps a transaction is started but neither committed nor rolled back.
@get_locks=1will give you an XML document showing which locks were acquired and being waited for, which might give a clue about what statements were run in the idle session.
@get_additional_info=1will also give you the isolation level.