none
long running job was stopped and rollback process started. checkpoing was issued and what's the consequence

    Question

  • hi folks:

        One of my coworkers was running an ad_hoc job on a production database (simple model ) but was causing havoc. He decded to stop the long running job and the rollback process is taking for ever to finish .  He then manually issued the checkpoint command  in the database and this rollback process stopped .  Here is my questions:

    1. Can manual checkpoint   really stop the rollback process or is this just a coincidence?

    2. how to I check if Rollback process has completed with success or was interrupted by the checkpoint?  

    Thanks


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Wednesday, February 7, 2018 10:03 PM

Answers

  • Thanks Josh. Basically,  for this rollback, no matter how long it may take, we just have to wait it through, correct? 

    Is there any preemptive way to at least mitigate the negative effects? 


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Far as I know, there is nothing you can do but wait it out.  There are some things you can try if it's hopeless, but they tend to leave the database broken, need to restore after.

    The only way to mitigate it is to try shorter versions of any long queries to make sure you don't have to do any long rollbacks.  Or of course break up the long transaction into many smaller ones.  It's not ideal but sometimes it's the best option anyway.

    Josh

    ps - kind of an old stack of technologies you're using there, newer software on newer hardware might just run a whole lot faster, forward and backwards!
    • Edited by JRStern Wednesday, February 7, 2018 11:49 PM
    • Marked as answer by cat_ca Thursday, February 8, 2018 12:09 AM
    Wednesday, February 7, 2018 11:48 PM

All replies

  • I'm going to have to say it was a coincidence.

    SQL Server is pretty adamant about rollbacks, I've never heard of one not finishing, unless it also crashed the entire service.  You could check the error log, but I don't know of another way to validate that a rollback completed properly at the system level.  Of course you can check your data.

    Josh


    • Edited by JRStern Wednesday, February 7, 2018 10:15 PM
    Wednesday, February 7, 2018 10:13 PM
  • Thanks Josh. Basically,  for this rollback, no matter how long it may take, we just have to wait it through, correct? 

    Is there any preemptive way to at least mitigate the negative effects? 


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Wednesday, February 7, 2018 10:31 PM
  • Thanks Josh. Basically,  for this rollback, no matter how long it may take, we just have to wait it through, correct? 

    Is there any preemptive way to at least mitigate the negative effects? 


    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Far as I know, there is nothing you can do but wait it out.  There are some things you can try if it's hopeless, but they tend to leave the database broken, need to restore after.

    The only way to mitigate it is to try shorter versions of any long queries to make sure you don't have to do any long rollbacks.  Or of course break up the long transaction into many smaller ones.  It's not ideal but sometimes it's the best option anyway.

    Josh

    ps - kind of an old stack of technologies you're using there, newer software on newer hardware might just run a whole lot faster, forward and backwards!
    • Edited by JRStern Wednesday, February 7, 2018 11:49 PM
    • Marked as answer by cat_ca Thursday, February 8, 2018 12:09 AM
    Wednesday, February 7, 2018 11:48 PM
  • Thanks Josh

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

    Thursday, February 8, 2018 12:09 AM