If you remember, I posted some tips about troubleshooting Link Server issue for Oracle earlier. Today, I will be adding one more tip to the list. In other words, I will extend Troubleshooting Oracle Link Server.
A few weeks back, a developer reached out to me needing assistance with an Oracle Link Server. The issue for him was that whenever he executed T-SQL against Oracle Link Server, the SQL Server was giving him the error OLE DB provider “OraOLEDB.Oracle” for linked server “XXXXXX” returned message “Cannot start more transactions on this session.” I connected to the server using SSMS and checked the permissions, Link Server, and Provider configuration – nothing was changed. Then what could have caused this error? I will need to RDP to the box to find the answer.
After asking some questions, I came to the realization that developers do not have direct access to the box and were using Citrix to connect to the SQL Server box. Therefore, they would not have any idea of what was going on with the physical box. As a DBA, I had privileges to RDP the development box. As soon as I logged in, I noticed that the box was running out of space. The drive that was running out of space was hosting:
- User Profiles
- & UserDB, which they were using for ETL purpose for Oracle
And, as expected, once I reclaimed some space, everything was back to normal.
Hemantgiri S. Goswami