December 13, 2013 at 4:43 am
Hi
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.
thanks
December 13, 2013 at 5:18 am
Use Select * from sys.databases
Find the dbid
Select * from sys.sysprocesses where dbid = dbid
Find the spid number
kill spid number
If the spid is a minus number you have an ORPHANED Distributed Transaction.
I will explain how to kill that if that is the case
December 13, 2013 at 5:55 am
You can use the KILL command to kill a session or get a report of the progress of a previously-issued KILL command. See http://technet.microsoft.com/en-us/library/ms173730%28v=sql.100%29.aspx. They cover orphaned transactions.
December 13, 2013 at 6:13 am
Talib123 (12/13/2013)
Use Select * from sys.databasesFind the dbid
Select * from sys.sysprocesses where dbid = dbid
Find the spid number
kill spid number
If the spid is a minus number you have an ORPHANED Distributed Transaction.
I will explain how to kill that if that is the case
KILL xxxx
December 13, 2013 at 4:40 pm
Talib123 (12/13/2013)
If the spid is a minus number you have an ORPHANED Distributed Transaction.I will explain how to kill that if that is the case
Cool... Let's hear it!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply