SQL Server – Resolving Orphaned Transaction (SPID -2) with UOW ID 00000000-0000-0000-0000-000000000000

1 - can't say for sure, I'd have to go find a server to dig into myself.

2 - yes, I see this periodically in my environment though we're not on sql 2012 yet on the systems we see this from. You may also want to check this post though State 46 seems to be related to having a specific Database=xxx in the connection string, does that db still exist?

The way my network is set up I suspect it's the network's automatic closing of tcp sessions after being 5 minutes idle that's the issue - neither the db nor the client is closing the session so the connection pool still thinks the connection is open and tries to use it only to find it's not really open anymore. You don't mention how the network between your web servers and db is configured, maybe your case is similar.

Another possibility may be the (old, not sure if ever really resolved, see http://support.microsoft.com/kb/942861) issue about TCP Chimney Offload settings.

3 - My understanding is pooling requires exact string matches, so whitespace and different order of parameters would cause different pools. (If I'm wrong on that, please let me know.)

I was able to reset the memory by establishing a DAC connection and without ever resorting to the -f startup parameter. However, I had to be persistent:

• At 128 MB, you will get all kinds of errors in the error log:

SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

(repeat for other caches)

Failed allocate pages: FAIL_PAGE_ALLOCATION 1

Verbose DBCC MEMORYSTATUS dumps

errors about audit / extended events data potentially lost

There is insufficient system memory in resource pool 'internal/default' to run this query.

• In some cases, the errors will be persistent enough to simply shut SQL Server down (and nothing will be recorded in the log). This happened twice during my experiments, and may also be influenced by the size of the databases attempting to be recovered on subsequent restarts. You should check whether any of the errors you receive while attempting to establish a DAC connection actually stem from the service not running (or perhaps in the process of shutting down).
• In each case, I had to go to Configuration Manager and restart the service. On the second try, I think the service shut down (or was in the process) before I established the DAC connection. On the third try, there was a lot less recovery action on startup, and I was able to establish a DAC query window and adjust max server memory, then when I restarted, everything went back to normal.
• Note that I was using 11.0.2376, and I recommend you perform this kind of edge case testing on builds you are likely to run in production. (Things like this may have been fixed post-RTM, and not all fixes are public.) If you're running RTM in production, then I kind of take that back, and will suggest that you reconsider that approach.

If you find that in your case you are also experiencing this "oops, SQL Server is no longer running" symptom, you could probably attach a debugger and determine exactly how SQL Server is shutting down.

So, can you do it without -f? Yes. Is it any cleaner? Only if you're really lucky. If your databases are large you are more likely to want to have a predictable number of restarts (2) because the recovery process could be a lot more involved than mine

Of course, an even cleaner thing would be to not mess around with max server memory using the GUI or ad hoc scripts that are prone to fudges - in production you want use a second set of eyes and have proper, validated, source controlled scripts that are much more predictable than "playing around." :-)