Post reply

Remote query never ends and cannot be killed

  • November 7, 2011 at 2:56 am

    #245690

    I'm working with a linked server to DB2/400 and sometimes 4-part-name queries execute forever and don't respond to kill command.

    The request remains in KILLED/ROLLBACK state and there's no other way to stop it than restarting SQLServer.

    I wonder if there's a "surgical" way to kill the process better than the "butcher" approach I'm using.

    I don't know what would be useful to troubleshoot the issue and I don't know what to include in this post, but I'll be glad to integrate it with any information you ask.

    Any help would be greatly appreciated.

    -- Gianluca Sartori

  • November 7, 2011 at 10:55 am

    #1404102

    Sometimes same story happens with sql server linked server (not DB2 only).

    I would try: 1) kill process from both sides (Src/Dest) and 2) disable/delete linked server

  • November 7, 2011 at 11:48 am

    #1404124

    I am not sure it will show up here, but if you look at the local MSDTC you might see a transaction in progress that you can kill. However I think it probably won't.. I had similar problems when connecting to DB2 on an OS/390 and later a Z OS machine. With SQL <-> SQL it is often MSDTC not configured to allow remote connections, which unfortunately manifests as queries starting but never finishing or dying.. I've run into this often with new SQL boxes.

    CEWII

  • November 7, 2011 at 4:57 pm

    #1404216

    Thanks Elliott, I already have tried that, with no luck.

    But you made me remember a quite important thing:

    The provider gets loaded out of process and I should probably kill the surrogate host instead of the MSDTC.

    I'll give it try tomorrow.

    Thanks

    Gianluca

    -- Gianluca Sartori

  • November 7, 2011 at 5:43 pm

    #1404230

    This all works kind of like a call to xp_CmdShell. In order to kill the process, you have to kill the spid that made the call (which you're already done) and you have to find the spid that the call is working under. It's more of an art than a science to find the second spid the first time but, once you find it, you'll have a better idea of what to look for.

    --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.

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

  • November 8, 2011 at 1:56 am

    #1404297

    I guess you're right Jeff.

    It should be DLLHOST.EXE (default surrogate for out-of-process load). Since I have many of these running, choosing the right one to kill would be a russian roulette. It think it's something between art, science and lottery. 😛

    -- Gianluca Sartori

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

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