They said that they are seeing below error in the System Event log and during that time they were not able to connect to SQL Server.
Event ID: 7011
Message: A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSSQLSERVER service.
Once it happens, they were not able to stop SQL Server service. I asked about how do they reproduce the error or hang situation and strangely they said that it happens when they expand an Oracle Linked server in SQL Server Management Studio!!!
I told them to reproduce the error. As soon as they expand “catalog” under linked server to oracle, it was stuck in “expanding”. Luckily, I was with them and as soon as “hang” was reproduced, I connected via DAC connection. I was able to see PREEMPTIVE_OS_GETPROCADDRESS wait for the SSMS query. As per my internet search, it is called when loading DLL. In this case, the wait for increasing continuously. So I asked them to kill the SQL process from task manager.
As a next step, I wanted to know which is the DLL causing issue, so I captured Process Monitor while reproducing the issue. Finally, we were able to nail down that SQLServr.exe is trying to find “OraClient11.Dll” but not able to locate it.
It didn’t take much time to conclude that the hang was caused due to incorrect value in PATH variable for Oracle DLLs used by linked server. This is also explained here.
We found that PATH variable was not having C:\oracle11\product\11.2.0\client_1\BIN which was the folder contains OraClient11.Dll. As soon as we added above location to PATH variable, issue was resolved.
Reference: Pinal Dave (https://blog.sqlauthority.com)