Unable to Kill inactive session.

  • lazy writer

    Ten Centuries

    Points: 1126

    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

  • Super Cat

    SSCertifiable

    Points: 7316

    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

  • Ed Wagner

    SSC Guru

    Points: 286985

    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.

  • Super Cat

    SSCertifiable

    Points: 7316

    Talib123 (12/13/2013)


    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

    KILL xxxx

  • Jeff Moden

    SSC Guru

    Points: 997149

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply