Stop Recovery Progress. Urgent help needed

  • 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.

  • 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?

  • 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.

  • 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

  • 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.

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

  • Did you solve your problem?

  • 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

  • "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

  • 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.

  • 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

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

  • 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...

  • 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
  • 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