SQL Server Agent – Managing Thousands of Sleeping Connections

SQL is usually pretty good about closing linked server connections. BUT, I've seen SQL server 'hang' linked server queries of certain types if all of the result set isn't fully fetched and/or closed properly by the client. IOW, SQL doesn't think that the app has finished retrieving the data, so it doesn't close the connection to the other server. For whatever reason, the offending app didn't report any errors or query time outs (from it's perspective), but we did have to recycle the IIS application every night or it would eventually eat up all of the RAM on the IIS server.

I would start by running my usual "find any connection that is waiting" query:

SELECT 
@@servername 'ServerInstance',es.session_id,er.blocking_session_id
,es.status
,es.login_name,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name,er.command 
,es.reads,es.writes
,es.cpu_time
,er.wait_type,er.wait_time,er.last_wait_type
,er.wait_resource
,CASE es.transaction_isolation_level WHEN 0 THEN 'Unspecified' 
    WHEN 1 THEN 'ReadUncommitted' 
    WHEN 2 THEN 'ReadCommitted' 
    WHEN 3 THEN 'Repeatable' 
    WHEN 4 THEN 'Serializable' 
    WHEN 5 THEN 'Snapshot' 
    END AS transaction_isolation_level 
,last_request_start_time
,last_request_end_time
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2, 
    (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2 
        ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text   
,ph.query_plan
FROM sys.dm_exec_connections ec 
  LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
  LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
  OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
  OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id <> @@SPID 
-- AND es.status = 'running'
AND (
es.status != 'Sleeping'
OR wait_time > 0
OR es.session_id in (select blocking_session_id from sys.dm_exec_requests where  blocking_session_id != 0)
)
ORDER BY es.session_id

I'd look for any sort of wait type like "OLEDB" (which should indicate linked server activity, and possibly other things) and try to tie those back to code that accesses the oracle server. I'd also pay attention to the last request start and end times.

So there's a couple of ways that this can happen.

1. The queries in the blocking database reference objects within the blocked database.
2. The queries are waiting for tempdb to allocate pages and one query is blocking another query.
3. There is memory pressure and queries are waiting for memory to be allocated so they have to wait their turn in the memory allocator.

If you download and put sp_whoisactive on the server it'll give you a lot more information about why things are blocking if this happens again.

Given your note about the memory pressure you probably were suffering from either #2 or #3 on my list above. Odds are it was memory not being able to be allocated fast enough, but it's possible that because you didn't have enough memory stuff was spilling to tempdb faster than tempdb to do "stuff".