Post reply

Linked Server Query Hung in Killed/Rollback

  • May 23, 2013 at 1:33 pm

    #293737

    I have a SQL 2008 R2 production database where a sql agent job kicks off some stored procedures. One of the procedures is to update the tables on the development database. The problem happens when the job on Production is manually killed. The command running on the Dev database receives the kill signal but it just hangs and does not close out ultimately consuming all of the memory 12GB allocated to the instance. Restarting the sql service usually fails and I end up rebooting the Dev box.

    Can someone explain why the linked connection query would hang as it keeps happening?

  • May 23, 2013 at 1:38 pm

    #1618027

    killing a linked server action that is using a distributed transaction pretty much guarantees a reboot is required to complete the rollback.

    it depends on what the query is actually doing as far as taking too long; but you probably need to remember that if you use data form a linked server in a query,unless you do some extra steps, every row int eh tables on the linked server get copied over to your local temp directly, and then the joins and filters are performed,and finally the operation it was doing(insert/update/delete) gets performed.

    if you linked over to a MillionBillionRow Table, you are probably waiting for enough memory and swap space to hold that table in temp and RAM, and it's taking forever.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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