Database (In Recovery..)

  • My suspicion is like Grant's. You are in the undo phase of recovery. I can't test right now, but I thought there were periodic messages, but can't find a reference to explain what is logged during recovery.

  • Does this give you anything? Not sure it will help, but curious

    http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/30/when-will-my-backup-restore-index-reorganize-finish.aspx

  • Guys.. fro mthe past 2 days the database is still in the recovery mode wihtout any message in the log, of course the db size is more than 2TB and just the log is around 900gb which is in simple recovery mode.

  • is there any other option i can do with the existing mdf file and want to get rid if the log file which is too big, i am not worried about losing any transactions that are in progress.

  • GilaMonster (6/14/2011)


    Post the latest error log (you can disguise the names of the DBs, they're not important)

    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
  • Tara-1044200 (6/15/2011)


    is there any other option i can do with the existing mdf file and want to get rid if the log file which is too big, i am not worried about losing any transactions that are in progress.

    You should be worried about the transactions in progress. If you do anything to that log (like delete it), the database will NOT be able to come back online at all.

    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
  • Do what Gail said about posting the error log. And you still haven't answered Grant's question, that I can tell. Why was the server shut down?

    You said you don't have enough disk space to recover that one DB (if I read the post correctly). How many DBs are on that disk? If just the one, you may be stuck (I don't think you can add more database files on other disks).

    If you have other DBs, do you have another disk you can plug in and move those DB files off so this DB can use the space to finish recovering?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • OK guys.. now i see the log saying database is receovring 11% not sure how long it would take, it is 2.5TB database.

    Also i have observed that SQL server Agent is also not working which is giving an error "cannot perform this operation while sqlserveragent is starting" and when i looked in the SQLAGENT.OUT file it shows "? [393] Waiting for SQL Server to recover databases...". This does not makes senseto me, does the sql agent not work when there is problem with any of the database on the server?

  • There will be a time estimation after the %. It's usually on the low side.

    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
  • Gila, at this point i am more worried about sql agent as there are many jobs that has to kick off over the week end, its ok for me even the database takes 3-4 days to recover. I am trying to understand why sql agent is depending on the recovery of the database.

  • do you use DTC anywhere in the applications?

    we had an issue before where there was a never terminating DTC transaction ongoing and it wouldn't terminate by killing the spid and it went into a -2 spid so trying to kill the UOW didnt work either.

    we rebooted the server and the databases was stuck in recovery mode even though it hit 99% recovered, it just wouldn't do that last check.

    we eventually terminated the DTC transaction by taking down SQL again and terminating the transaction via component servicers, rebooted SQL all was good.

  • Check the logs, see if there are any messages for MSDB. Check the state of MSDB. SQLAgent shouldn't depend on a user DB.

    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

Viewing 12 posts - 16 through 26 (of 26 total)

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