I've started annoying by an issue where I couldn't terminate a remote query session. My requirement was to download data from a remote oracle server via Linked server(used oracle native drives here) crated on SQL Server.
whenever there was a network bottleneck, session will become inactive and will run for days showing OLEDB wait type eg:(163214842ms)OLEDB. Ultimately this will get cleared only by restarting SQL Services, of course no one like to do so 😛
Even though it is not consuming any resources on server, it has been keeping an excursive lock on the destination table therefore I coldn't use TRUNCATE table or DROP this staging table.
any ideas would be greatly appropriated.