Database in recovery mode

  • Actually, Sql server was not responding in the morning so we restarted the server but one of our database is in recovery mode. I use sp_who2 and get following result.

    spid : 13

    tatus : background

    blk by : 13

    command : awaiting command

    cpu time : 0

    diskIO : 482529

    Is there a way to know how long that takes to finish recovery.?

  • Check the disk space once...whether it has enough space or not?

  • Have a look in the error log. What does it say about that database?

    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
  • It did not say anything other than blocking. Let's say if you cannot connect sql server then how should we trouble shoot the issue. where to look for the issue. I checked the windows event log but did not see any major issue. This is sql 2000 server.

    I just rebooted the server and everything works fine but I don't think it's a good idea to do that.

  • balbirsinghsodhi (11/4/2008)


    It did not say anything other than blocking. Let's say if you cannot connect sql server then how should we trouble shoot the issue. where to look for the issue. I checked the windows event log but did not see any major issue. This is sql 2000 server.

    The error log should have entries in something like

    "Recovery of Database X is Y% complete estimated time Z seconds"

    I must say I've never seen blocking reports in the SQL error log. Can you post one of those lines?

    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
  • you have to be carefull before restarting SQL server, Make sure that there are more space for the database to restore itself, it takes some time for restore, as Giall suggested you can see on the error log

    Cheers

    🙂

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

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