Post reply

Query cancelled but completed is 0...

  • August 31, 2017 at 4:33 am

    #348879

    Hey,
    Just got another issue. One of our developers ran a BI query directly on the server and cancelled it after 15 Hrs as our daily full backup ran abnormally longer. It was an update on table with 30 million rows and has a join on another table with 200k rows . Unfortunately the officer didn't run a select to have an idea how many rows it was supposed to update. Now the issue is though our fullback finished in 12 times longer time , this query that was cancelled didn't rollback it seems. The re-issuance of the KILL sessionID command says it killed but the completed percent appears as zero . Any comments , suggestions on this ?

    Thanks..Arshad

  • August 31, 2017 at 5:48 am

    #1957622

    you have to be patient. it finishes rolling back when it finishes. maybe 15 to 30 hours from now; it's common that a rollback can take twice as long as the executing command took before it was canceled.
    if you stop and start the service, you could be waiting even longer, and even worse, be preventing any access at all while the database rolls back the pending transactions.

    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!

  • August 31, 2017 at 6:43 am

    #1957634

    Please, listen to what Lowell said. Don't shut off the server. I see that happen so often and it just makes this situation worse. Wait. It will clear eventually.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 31, 2017 at 6:54 am

    #1957636

    Grant Fritchey - Thursday, August 31, 2017 6:43 AM

    Please, listen to what Lowell said. Don't shut off the server. I see that happen so often and it just makes this situation worse. Wait. It will clear eventually.

    Thanks Lowell and Grant . I take heed to the warning. Thank u so much.

Viewing 4 posts - 1 through 3 (of 3 total)

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