http://www.sqlservercentral.com/blogs/sql-server-citation-sql-blog-by-hemantgiri-s-goswami-sql-mvp/2012/09/01/extending-troubleshooting-oracle-link-server/ Printed 2017/08/20 10:00AM
Troubleshooting Oracle Link Server - Part2
It's my bad that I did not made a post since long lately. I would not make an excuse like I was busy, rather, I would say I am little lazy DBA, but I now onward I will try to post blog more frequently.
Few weeks back one of the developer reached out to me needing an assistance with Oracle Link Server. The issue with him was, whenever he execute T-SQL against Oracle Link Server, SQL Server was throwing an error at him which reads like OLE DB provider "OraOLEDB.Oracle" for linked server "XXXXXX" returned message "Cannot start more transactions on this session." I'd connected to the server using SSMS and have checked permissions, Link Server and Provider configuration - nothing was changed. Then what could have cause this error? well, I will need to RDP to the box then to find an answer.
But before that, as usual, upon questionnaire session, I came to know that developers doesn't have direct access to box and they all were using Citrix to connect to the SQL Server box hence they would not have any idea on what's going on with the physical box. As a DBA I had a privileges to RDP to the development box (obviously ), as soon as I logged in I'd notice that the box is running out of space. The drive which was running out of space was hosting:
- User Profiles
- & UserDB which they were using for ETL purpose for Oracle
And, as expected, once I'd reclaim some space everything was back to normal.