SQL Server 2014链接Oracle 11的OLEDB线程挂起登录问题求助

Let’s walk through diagnosing and fixing this issue where over 50 SQL Server threads have been stuck waiting on OLEDB linked server connections for 3+ hours—even though Oracle reports no active queries from those sessions.

Step 1: Pinpoint Exact Wait Details in SQL Server #

First, get granular info on what the stuck threads are actually waiting for with this query:

SELECT 
    session_id,
    wait_type,
    wait_time/1000 AS wait_time_sec,
    last_wait_type,
    command,
    text AS query_text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE wait_type LIKE '%OLEDB%' OR wait_type LIKE '%LINKED%';

Look for wait types like OLEDB_COMMAND, OLEDB_WAIT_FOR_DATA, or LINKED_SERVER_QUERY—this will tell you if the hang is tied to command execution, data retrieval, or connection setup.

Step 2: Audit Linked Server Provider Settings #

Stale connection pooling or misconfigured OLEDB properties often cause these hangs. Check your Oracle provider settings:

• In SQL Server Management Studio, navigate to Server Objects > Linked Servers > Providers > OraOLEDB.Oracle
• Right-click > Properties
• Ensure Allow inprocess is enabled (this avoids cross-process communication glitches)
• If Pool connections is turned on, try disabling it temporarily to rule out stale pooled connections
• Restart the SQL Server service after changes (schedule this for a maintenance window if possible)

Step 3: Verify Oracle Session State and Network Stability #

Even if Oracle says no active queries, check for orphaned idle sessions on Oracle:
Run this on your Oracle instance to find sessions tied to your linked server account:

SELECT 
    sid, serial#, status, last_call_et, program
FROM v$session
WHERE username = 'YOUR_LINKED_SERVER_USER' AND program LIKE '%SQL Server%';

If you see sessions with status = 'INACTIVE' and high last_call_et, these are likely dead connections SQL Server is still waiting on. Terminate them with:

ALTER SYSTEM KILL SESSION 'sid,serial#';

After killing these, check if SQL Server threads unblock.

Also validate network health:

• Run ping -t between SQL Server and Oracle to check for packet loss
• Test TCP connectivity on Oracle’s port (usually 1521) with telnet oracle_server 1521 or PowerShell’s Test-NetConnection
• Confirm firewalls aren’t dropping idle connections after a timeout—many firewalls kill idle sessions after 1-2 hours, leaving SQL Server hanging on dead links.

Step 4: Set Linked Server Query Timeouts #

By default, linked server queries may have no timeout, leading to infinite waits. Add a timeout to prevent this:

EXEC sp_serveroption 'YOUR_LINKED_SERVER_NAME', 'query timeout', 300; -- 5 minutes in seconds

Step 5: Update the Oracle OLEDB Provider #

Outdated OraOLEDB.Oracle versions often have connection-handling bugs. Ensure you’re using the latest compatible provider for Oracle 11g and SQL Server 2014 (always test updates in a staging environment first).

Immediate Workaround #

If you need quick relief, manually kill stuck SQL Server sessions with:

KILL session_id;

Only do this for sessions confirmed to be stuck on linked server waits—avoid killing sessions that could cause data inconsistency.

内容的提问来源于stack exchange，提问作者rsreji