Database in Recovery mode

  • HI,

    My developer executed a stored proc and its been almost 24 hrs but its still spinning, so i restarted the MSSQL Server service and after restart my database on which stored proc is running went inot recovery mode. its been 8 hrs and no change. Please provide me the solution, how to bring back my database online and unfortunately we dont take any db backups...

    Thanks

    Praneeth

  • praneethydba (1/20/2014)


    HI,

    unfortunately we dont take any db backups...

    Why? How did you expect to be able to recover the database if it went wrong?

  • HI,

    As the database is very big and no place on drive. we stopped taking backup's.

    Can you please provide me the solution..

    Thanks

    Praneeth

  • Wait.

    There is nothing else you can do, particularly since you have no backup. You have to wait until recovery is complete.

    When you restarted the server all you did was force SQL to do the rollback of the operation after the restart, this time with the DB unavailable to anyone. It usually takes longer to roll back than the initial operation took, so be patient and don't restart it again, you'll just make it start over.

    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
  • praneethydba (1/20/2014)


    As the database is very big and no place on drive. we stopped taking backup's.

    So what do you expect to do when something goes seriously wrong and the DB is lost or damaged? Or is the entire of that very big database easily replaceable without any business impact?

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

    Thanks for the reply..

    I totally agree with you. We have to take db backup. there is an Impact on the business as week starts from today. My concern is will it be back online or will be go to suspect/damage of my database.

    I will wait for 3 more hours, but if i come online well and good, if not is there any other alternative solution inorder to bring back my database usable i.e. online.

    Its confirmed that my database will go turn to suspect mode or damaged.

    Thanks

    Praneeth

  • Wait until recovery finishes!

    There is no alternate solution. There is no magic 'instantly finish recovery' button. Be patient and wait for the recovery to complete, then your DB will come online. If you restart SQL again all you're going to do is force SQL to start recovery over from the beginning, hence wasting the last 8 or so hours.

    Wait. And while you're waiting, sort out your backup strategy.

    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
  • Is your database back online?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • No... Not yet.

    Its still recovery. I told the scenario to my boss. Just we are all waiting for the database to be online.

    Thanks

    Praneeth

  • Hopefully your boss will see the urgency of taking backups now!

  • While waiting for it to come online, have you checked the disk system? Have you checked general server health? Have you checked permissions (make sure they haven't changed)?

    Your recovery process could take a very long time based on the info provided. While that is happening, you might as well do everything possible to ensure there is nothing else causing delays or problems.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • praneethydba (1/20/2014)


    HI,

    As the database is very big and no place on drive. we stopped taking backup's.

    Can you please provide me the solution..

    Thanks

    Praneeth

    The best backups are not stored on the same drive, IMHO. And with the cost of space these days........

  • HI,

    Still in Recovery. I am Panic.

    My company is running this database from past 8 years and no indexing, no Primary/clustered index, No jobs etc.

    So I want to streamline all this database. I studied on net to improve performance we can do table partition for fast retrieval of data.

    Can you please let me know the link or site

    Any additional tips are highly appreciated!!!

    Thanks

    Praneeth

  • praneethydba (1/21/2014)


    Still in Recovery. I am Panic.

    Be patient.

    The progress gets logged to the error log, check there for estimated time remaining, but note it's often on the low side

    My company is running this database from past 8 years and no indexing, no Primary/clustered index, No jobs etc.

    Sounds like you need to get someone in to evaluate and make recommendations. If it's that much of a mess, it's going to be a huge job just figuring out where to start.

    I studied on net to improve performance we can do table partition for fast retrieval of data.

    Um, no.

    https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

    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
  • praneethydba (1/21/2014)


    HI,

    Still in Recovery. I am Panic.

    My company is running this database from past 8 years and no indexing, no Primary/clustered index, No jobs etc.

    So I want to streamline all this database. I studied on net to improve performance we can do table partition for fast retrieval of data.

    Can you please let me know the link or site

    Any additional tips are highly appreciated!!!

    Thanks

    Praneeth

    Partitioning is not really a performance tuning mechanism. It's primarily a data management mechanism that can have performance improvements. I agree with Gail, get a professional in to do an assessment. Barring that, get a copy of my book. At least you'll have a functional starting point for figuring this stuff out.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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