Post reply

Stop Recovery Progress. Urgent help needed

  • March 26, 2008 at 8:33 pm

    #120603

    One of my database is in recovery process. I need to stop it. I don't care if I lose data in log file. I want to delete log file but cannot.

    Is there a way to stop recovery process.

  • March 26, 2008 at 8:55 pm

    #794422

    Pull the plug! Not sure that you can do anything else.

    There is a switch to start SQL Server without going through recovery.

    Why do you want to stop it? This is recovery, on the restart of the instance and not restore, correct?

  • March 26, 2008 at 9:05 pm

    #794428

    Is it recovering uncommitted transaction from the transaction log file. If that is the case then I can open database with out transaction log file and let system will create a new log file.

    My log file is growing and no space on the drive.

    I don't know what recovery process is doing and how long it takes.

  • March 26, 2008 at 11:10 pm

    #794448

    I think I am late in party 🙂

    I would never recommend to stop recovery process. No one can stop you doing suicide but thats crime 😎

    Jokes apart, You could stop SQL Server in such cases, rename the transaciton log. This will bring database into suspect mode. Then you can run some command to rebuild the log after brining database in to Emergency mode. SQL 2005 has ALTER DATABASE SET EMERGENCY command.

    Let me know if you want more details - bmlakhani@yahoo.com

  • March 27, 2008 at 2:23 am

    #794473

    As of now there is no proper way to quit from an ongoing restore....if anyway you interrupt the restore process, the state of the database will be set to 1 and the database property 'IsShutDown' for the database is set to null....and there is no way of updating the system tables with adhoc queries......so its upto the MS SQL Server Development team to provide the feature of restore rollback in the next version......

    Chandrachurh Ghosh
    DBA – MS SQL Server
    Ericsson India Global Services Limited
    Quality is not an act, it is a habit.

  • March 27, 2008 at 2:45 am

    #794481

    you can use trace 3607; it should skip the recovery process. Hope you have the backups in place:)

  • March 27, 2008 at 5:23 pm

    #794948

    Did you solve your problem?



    My blog

  • March 28, 2008 at 7:09 am

    #795145

    I suspect that a recovery was started and left so that more logs could be applied.

    RESTORE DATABASE [analysis] WITH NORECOVERY

    I would suggest trying to recover to read only mode first.

    RESTORE DATABASE [analysis] WITH STANDBY = 'D:\Program Files\Microsoft SQL Server\SQL2005\MSSQL.1\MSSQL\Backup\ROLLBACK_UNDO_analysis.BAK'

    Or just try and fully recovery

    RESTORE DATABASE [analysis] WITH RECOVERY

  • May 9, 2008 at 9:45 am

    #813175

    "Then you can run some command to rebuild the log ...." and that is? I'm having the same issue and I was able to rename the log but was not sure how to rebuild the log.

    Your help is greatly appreciated

    Kurt

    .

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • May 9, 2008 at 11:37 am

    #813261

    Thanks for all your help.

    The recovery process ran for some time and then recovered the database. I did not do anything but to wait.

  • May 9, 2008 at 11:44 am

    #813268

    How long did you wait? Mine has been recovering for quite some time now (5-6 hrs)

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • May 9, 2008 at 11:53 am

    #813276

    I waited for 4 hr. It depends how big database you have. I think let it run and wait for some time.

  • January 8, 2010 at 11:24 am

    #1101093

    Reasons why database recovery will take longer than normal:

    1. when you restart you database and you are processing transactions. You should stop all transaction 1ft before restaring you sql

    2. you database and log files are HUGE.

    And yes, all you have to do is wait...

  • January 8, 2010 at 1:43 pm

    #1101172

    You did notice this thread is over a year and a half old?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 8, 2010 at 1:46 pm

    #1101176

    Not really, I just encounter this problem today 😉 man you are so harsh

Viewing 15 posts - 1 through 15 (of 16 total)

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