Post reply

Recovery Model

  • March 31, 2011 at 4:08 am

    #235978

    Hi ,

    Im using sql server 2005. I tried to update one column using substring function it contains some 9500000 records.

    It took more than 1 hour and immediately i have tried to cancel the query.That time it goes into the recovery model.

    How much time it will take to recover. Im not able to retrieve the data from the database.

    Is there any solution to get the database to normal condition.

  • March 31, 2011 at 4:17 am

    #1305882

    Recovery takes longer than the action itself.

    The server has to read the transactionlog, apply the redochanges (which are logged also?).

    To delete something from large tables it is benificial to do so in (small) batches so you don't risk running out of transactionlogspace during the operation.

  • March 31, 2011 at 4:22 am

    #1305884

    If you know the session ID (SPID) of the process, you can find the percentage complete of the rollback (approximately) by looking at the following DMV:

    SELECT percent_complete FROM sys.dm_exec_requests WHERE session_id = mySPID

    If you ran this in Management Studio, your SPID is the number in brackets next to your username underneath the results window

  • March 31, 2011 at 4:36 am

    #1305886

    prakashmohandoss86 (3/31/2011)

    Im using sql server 2005. I tried to update one column using substring function it contains some 9500000 records.

    It took more than 1 hour and immediately i have tried to cancel the query.That time it goes into the recovery model.

    Just cancelling a query won't send the database into recovery. Someone must have restarted the SQL service too.

    Recovery takes as long as it takes, there's no speed up, there's no way to stop it, recovery must be complete for the DB to come online. Check the SQL error log for details on how far the recovery is and how long it thinks is remaining.

    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

  • March 31, 2011 at 4:38 am

    #1305887

    How to view the sql error log file?

  • March 31, 2011 at 4:49 am

    #1305890

    Apologies, think I misunderstood the question then. Yes, someone must have restarted the SQL Service while the rollback was still completing, so it has to recover before the database can be opened. As Gail said, it'll take as long as it takes - you must allow it to complete.

    In regards to the location of the error log, please refer here:

    http://msdn.microsoft.com/en-us/library/ms187885.aspx

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

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