We created a linked server to connect SQL Server to our ERP (running UniVerse not SQL Server). It has worked fine in the past, but now any queries to the linked server just get stuck (for days) with an OLEDB wait type.
This has happened on rare occasions previously, and restarting the SQL Server has always resolved the issue. Now however, restarting the server either does not solve the issue or it fixes it for at the most an hour or two.
We are experiencing the issue in both SQL Server 2016 and 2008 R2.
We are able to query the data source using other tools on the same server and with the same driver as the SQL Server. On one server, we are running two instances of SQL Server 2008 R2. One is able to query the data source, while other gets stuck with the OLEDB wait type. The linked server on both is set up exactly the same way, and they are using the same driver.
We hare tried:
- Restarting SQL Server
- Restarting the server the data source is on
- Different drivers
- Recreating the linked server
- Querying the data source using a different tool using the same driver (it was successful)
- We are able to ping the data source server from the sever with the affected SQL Server
- We are able to connect to the data source using Telnet from the affected server
Best Answer
For SQL Server targets: On the target server (the one that's receiving the linked server query), run sp_WhoIsActive and look at the linked server query.
If the query doesn't show up, something's going awry with the linked server query and it's never reaching its destination.
If it does show up, look at the Wait column, and see what the wait type is. (It won't be OLEDB.) That'll help you troubleshoot the reason it's not completing. Feel free to post that in with your question, and I can elaborate more on that specific wait type.
For non-SQL Server targets like the IBM/Rocket one in this case: you'll want to work with the sysadmin/DBA on that platform, and have them check the progress of the query. When you see OLEDB on the SQL Server sending side, you just can't tell anything about what the holdup is on the other end.