Post reply

How to cancel Recovery process

  • September 9, 2004 at 5:14 pm

    #83971

    Hello friends. I have a very large process than I want to cancel, but the rollback process was taking a long time... next I had shutdown the SQL Service, but Recovery process is about 4% (since 5 hours). I am not working in a production environment, then I don´t need wait until recovery process complete. IS THERE ANY WAY TO definitively CANCEL THIS PROCESS?

    THANKS. any comment is welcome.

    JORGE

  • September 10, 2004 at 2:52 am

    #522473

    Hmm. If you cancelled a process, SQL Server would have started rolling back the changes. If you shutdown SQL Server during this process, then when it boots it's going to want to continue rolling back those changes. This is what data integrity is all about.

    I DON'T RECOMMEND DOING THIS: But if you don't need the database, you could stop sql server and delete all the physical files for the database and its logs. Then when it boots you could restore the database from a backup. Not sure if this would work though. You could end up with SQL Server not wanting to boot.

    Basically SQL Server has to complete the rollback. Maybe next time make your changes in smaller batches, and commit along the way.

    Julian Kuiters
    juliankuiters.id.au

  • September 10, 2004 at 5:24 am

    #522513

    you can turn on trace flag 3608, which will skip automatic recovery for all databases except the master database.

     

    ref:

    http://www.google.com/search?num=100&hl=en&lr=&ie=UTF-8&newwindow=1&safe=off&q=sql+trace+flag+3608+2000

  • September 10, 2004 at 8:31 am

    #522559

    A question: changing trace flag 3608 skips recovery only while trace flag is ON (I think: YES)? but I'd like DEFINITIVELY abort the recovery process (at any switch value of trace flag 3608) so I can continue use this database.

    Regards,

    Jorge

  • September 10, 2004 at 8:36 am

    #522561

    To Julian: First of all, thanks for your comments. Finally, I had to do something similar: First I started the sql server with command line -c -f, then I changed the db status to emergency mode (-327...) shutdown the server and restart again in normal mode. After, I dropped the database and finally restored a backup.

    In MSDN documentation I have read that in a database status = emergency mode it is possible to execute DUMP TRANSACTION WITH NO_LOG, but I'll try to do this (aparently, the solution!), with no success (err Message "BACKUP is not allowed in emergency mode").

    Regards

    JC

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

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