2

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
4

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.

  • The target server is a Universe Database (originally by IBM, now owned by Rocket Software) and not a SQL Server, – Fercstar Jan 23 '17 at 16:15
  • Ah, in that case, you'll want to do a similar task over on that target server - but you just can't see on the SQL Server side what the IBM/Rocket box is waiting for. – Brent Ozar Jan 23 '17 at 16:31
  • It is a fairly antiquated database system (based on SQL-89 with "elements" of SQL-92), but I can see a connection when I run a query from the SQL Server instance that has the working linked server. I do not see a connection when running a query from one of the instances with the OLEDB wait type. – Fercstar Jan 23 '17 at 17:44
  • Well, if you don't see a connection on the target box, now it's time to start network troubleshooting. Check to see if you can successfully ping, if it gets the correct IP address, try telnetting to the port of the database, etc. – Brent Ozar Jan 23 '17 at 21:35
  • Unfortunately, everything is normal pinging the server and connecting via Telnet. – Fercstar Jan 23 '17 at 22:09
0

We had a similar issue here. We had a sproc on server call another sproc on server and had varying issues with performance. We ran sp_WhoIsActive on the target server and no wait type showed up. We then added "SET ARITHABORT ON;" on the target server sproc and somehow this resolved the issue. Seems to be because of parameter sniffing. More detail on this here. Mainly this piece..."Your application connects with ARITHABORT OFF, but when you run the query in SSMS, ARITHABORT is ON and thus you will not reuse the cache entry that the application uses, but SQL Server will compile the procedure anew, sniffing your current parameter values, and you may get a different plan than from the application".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.