locked
immortal query with Oracle linked server RRS feed

  • Question

  • Hi, All

    I have

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
        Jun 28 2012 08:36:30
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

    On this server linked server to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production is configured.

    Sometimes query which used this linked server hangs up. I do command "kill", but query doesn't kill and show message "query is rolled back.....Estimated rollback time is 0". In this mode query can be for unknown time.Server restart solves this problem, but I would like to find simpler way.

    The question is: how to kill this "immortal query"?

    Thursday, April 2, 2015 7:07 AM

Answers

  • The reason KILL does not work is that SQL Server employs cooperative multi-tasking. That is, every once in a while a process yields voluntary to permit other processes to be run, and this is also when it checks if it has been killed.

    But this only works as long as the processes is executing code inside SQL Server (where there are yield points in the code). If the process executes code in the operating system, or as in this case in an OLE DB provider for a different data source, it cannot yield, and it cannot be killed.

    The only possibility would be to find the corresponding process in Oracle and kill it. It seems that you have already tried that, and why did not work out, I cannot say. It might require some knowledge about Oracle to understand.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Michelle Li Wednesday, April 8, 2015 8:01 AM
    Thursday, April 2, 2015 9:48 PM

All replies

  • Why are you running such queries in first place. Yes this is issue I have seen and believe me only better solution I have find is to avoid ssuch queries.

    With Linked queries when queries execute on Oracle SQL Server has limited control on it and when you kill such query proper rollback does not happens.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, April 2, 2015 7:31 AM
  • In addition - rollback does not happen even if I kill query on Oracle
    Thursday, April 2, 2015 7:37 AM
  • In addition - rollback does not happen even if I kill query on Oracle

    Can you show me the query, did you checked the wait type. What it is waiting for ?

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Thursday, April 2, 2015 7:39 AM
  • In SQL Server's Activity Monitor for this query I see the following:

    "Wait Type"= "External ExternalResource=MSQL_DQ"

    "Query status"='KILLED/ROLLBACK'

    Thursday, April 2, 2015 7:48 AM
  • In SQL Server's Activity Monitor for this query I see the following:

    "Wait Type"= "External ExternalResource=MSQL_DQ"

    "Query status"='KILLED/ROLLBACK'

    The wait type corresponds to distributed query completion. You killed the query before it could complete and again I would reiterate you should not do that

    For more information about wait type please read This Blog

    Again what is the query, please make sure you post complete information read my comments carefull. I would say please wait and let it rollback


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by Michelle Li Thursday, April 2, 2015 11:17 AM
    Thursday, April 2, 2015 8:02 AM
  • The reason KILL does not work is that SQL Server employs cooperative multi-tasking. That is, every once in a while a process yields voluntary to permit other processes to be run, and this is also when it checks if it has been killed.

    But this only works as long as the processes is executing code inside SQL Server (where there are yield points in the code). If the process executes code in the operating system, or as in this case in an OLE DB provider for a different data source, it cannot yield, and it cannot be killed.

    The only possibility would be to find the corresponding process in Oracle and kill it. It seems that you have already tried that, and why did not work out, I cannot say. It might require some knowledge about Oracle to understand.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Michelle Li Wednesday, April 8, 2015 8:01 AM
    Thursday, April 2, 2015 9:48 PM
  • I have this issue too, but for AS400 linked server.

    You need to check OLEDB provider mode.

    If it has Out-of-process mode, you need to kill dllhost.exe which is dummy process for connection to external servers instead of sqlserver.exe

    Friday, August 11, 2017 6:36 AM