Database has gone in suspend mode how to recover...?

  • hi all,

    i have database size near by 110 GB and it has became suspect now i am trying to recover it by a batch job but it' running continious since 30 hours.

    how can i get it recover plese help me.

  • What does that batch job do?

    First thing we need to do before anything else is identify why it is suspect. Open up the SQL error log and look for all errors related to this database. SQL will have logged a message regarding why it marked the database suspect.

    Post any errors here, don't try any recovery methods that you may find for now.

    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
  • As GilaMonster suggested firstly you should have to know what is the exact cause or reason for the suspect mode. You can know the reason of suspect mode by this query

    "DBCC CHECKDB (’YourDatabasename’) WITH NO_INFOMSGS, ALL_ERRORMSGS".

    After knowing the reason, you can restore your suspect database by running the given below query in the query analyzer.

    "EXEC sp_resetstatus ‘yourDatabasename’;

    ALTER DATABASE yourDatabasename SET EMERGENCY

    DBCC checkdb(’yourDatabasename’)

    ALTER DATABASE yourDatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC CheckDB (’yourDatabasename’, REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE yourDatabasename SET MULTI_USER".

  • addisionphilip (5/11/2011)


    As GilaMonster suggested firstly you should have to know what is the exact cause or reason for the suspect mode. You can know the reason of suspect mode by this query

    "DBCC CHECKDB (’YourDatabasename’) WITH NO_INFOMSGS, ALL_ERRORMSGS".

    Nope. You find the cause of the suspect status by looking in the SQL error log. SQL will log the reason for marking the DB suspect.

    Do note that if you run the command you recommended on a suspect database, you will get an error saying that the DB has been marked suspect by recovery and cannot be opened

    After knowing the reason, you can restore your suspect database by running the given below query in the query analyzer.

    You cannot recommend a fix without knowing the cause. It may well be that repair is the only option, but it is not and should never be the first thing on the list.

    For more details you can read my blog here

    <snip>

    Do you want me to give you a list of the errors in that advertising disguised as blog post? There are many.

    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
  • I haven't (yet) gone to that page because of your comment. I for one WOULD like to know what's not right.

    Am I crazy to think that anything that causes a "suspect database" is a hardware problem or a SQL Server bug? Isn't the whole idea of writing the transaction log prior to writing to the database file, and keeping the log on a different spindle, and so on -- aren't those things done that way (at a non-trivial cost in performance) intended to make it so that even pulling the power cord of the server machine can't prevent SQL Server from recovering automatically (unless the disk hardware is damaged)?

  • jmerrill 28016 (5/16/2011)


    Am I crazy to think that anything that causes a "suspect database" is a hardware problem or a SQL Server bug?

    Yup. Pretty much. Most likely hardware (IO) problem.

    Suspect status is caused when SQL encounters corruption (data or log) during crash recovery or during a transaction rollback.

    Still, the first part of any fixing (corruption or suspect DBs) is to identify scope of the problem. That's by running CheckDB if the DB is online and just corrupt or by checking the error log if the DB is suspect. Once the problem is known, then a recovery plan can be considered.

    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
  • Other than the fact that it was an advertisement for a commercial product, what was particularly incorrect on the page you referenced?

  • jmerrill 28016 (5/16/2011)


    Other than the fact that it was an advertisement for a commercial product, what was particularly incorrect on the page you referenced?

    Advertising aside, isn't it pretty irresponsible to suggest running REPAIR_ALLOW_DATA_LOSS without knowing more information about the problem?

    The Redneck DBA

  • jmerrill 28016 (5/16/2011)


    Other than the fact that it was an advertisement for a commercial product, what was particularly incorrect on the page you referenced?

    At this point I have neither the time, nor the inclination to go and tech-review the post in detail. Have a read through some good corruption advice (http://www.sqlskills.com/blogs/paul/ and to a much lesser extent http://www.sqlservercentral.com/articles/65804/) and if you have any questions, please ask.

    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
  • Jason Shadonix (5/16/2011)


    jmerrill 28016 (5/16/2011)


    Other than the fact that it was an advertisement for a commercial product, what was particularly incorrect on the page you referenced?

    Advertising aside, isn't it pretty irresponsible to suggest running REPAIR_ALLOW_DATA_LOSS without knowing more information about the problem?

    Absolutely is. Repair is the last resort for fixing corruption, not a knee-jerk first reaction.

    It's kinda like a car mechanic replacing the engine if someone complains of misfires, without checking to see if maybe a spark plug needed replacing.

    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
  • I agree -- that certainly qualifies as bad advice (particularly without making sure that a backup of the .MDF is made, so that the commercial product probably won't be able to do anything once it's been done).

    I don't know where else to say it, so it's here -- I've been "cruising" the Data Corruption sub-forum, and I'm impressed by how much of your time and expertise you're passing on to the rest of us. I'm sure that it's appreciated by others too.

Viewing 11 posts - 1 through 10 (of 10 total)

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