none
Suspect Database

    Question

  • Hi 
         I am using third party tool for logshipping which is red gate, suddenly today one database moved in suspect mode and logshipping stopped for this database.
         when i run the Sp_resetstatus 'DBName' statement to change the state of database and restarted the sql services, it is showing me that database in Restoring mode.

         Pls help me what to do?

    Raj
    **Vote it if it is helpful. **Mark as answer this if it solves your problem. Raj_79
    Monday, July 27, 2009 11:41 AM

All replies

  • When i tried to restore transaction log manually, database again moved in suspect mode.
    Can anyone help me to resolve the problem?
    Monday, July 27, 2009 12:09 PM
  • Run sp_resetstatus 'DBname' again if your database in Restoring mode try to run
    Restore Database DBName with Recovery
    Monday, July 27, 2009 3:55 PM
  • Hello,

    Please try to put the database in Emergency Mode, with the following command:


    ALTER DATABASE DatabaseName

    SET EMERGENCY

    Then try to eliminate corruption on data files with the following commands:

    ALTER DATABASE DatabaseName SET SINGLE_USER;
    GO
    DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS)
    WITH NO_INFOMSGS;
    GO


    Hope this help.

    Regards,


    Alberto Morillo

    • Proposed as answer by vr.babu Thursday, January 17, 2013 5:06 AM
    Monday, July 27, 2009 3:59 PM
  • Hello,

    Please try to put the database in Emergency Mode, with the following command:


    ALTER DATABASE DatabaseName

    SET EMERGENCY

    Then try to eliminate corruption on data files with the following commands:

    ALTER DATABASE DatabaseName SET SINGLE_USER;
    GO
    DBCC CHECKDB (DatabaseName, REPAIR_ALLOW_DATA_LOSS)
    WITH NO_INFOMSGS;
    GO


    Hope this help.

    Regards,


    Alberto Morillo


    Make sure that you pay attention to the fact that this says REPAIR_ALLOW_DATA_LOSS which is very dangerous if you don't know exactly what you are going to lose.  You need to be careful running that on a production database.  You would be better served restoring to your last good backup of the database.  You likely have an underlying disk subsystem problem that you need to sort out here.  Look at your SQL Server ErrorLogs for indicators why the database went into suspect status.  Other places to look for information are the Windows System Event Log.  You don't want to just start whacking data with REPAIR_ALLOW_DATA_LOSS unless you know what exactly you are going to lose, or why you got into a suspect/corrupt state to begin with.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Monday, July 27, 2009 4:09 PM
  • I have tried above statement when i run the statement "ALTER DATABASE DatabaseName SET EMERGENCY", it is giving following error

    Msg 5052, Level 16, State 1, Line 1
    ALTER DATABASE is not permitted while a database is in the Restoring state.
    Msg 5069, Level 16, State 1, Line 1
    ALTER DATABASE statement failed.


    Is there any way to online suspect database?

    Raj


    **Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79
    Tuesday, July 28, 2009 5:41 AM
  • Hello Raj,

    Please try the following command to try to put the database online again:

    RESTORE DATABASE DatabaseName WITH RECOVERY

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, July 28, 2009 12:01 PM
  • Hi Raj,

    Have you gone through the logs generated by the "Red gate" tool. As you have mentioned that the database was being log shipped though this third pary application.


    As mentioned by "Alberto" this statement :-

    RESTORE DATABASE DatabaseName WITH RECOVERY


    should bring it to normal mode as in log shipping database is restored with no recovery, so it shows you restoring.

    Thanks

    Kishan Singh
    Tuesday, July 28, 2009 4:39 PM
  • May be above statement can solve the problem. Because it was some urgent and database size was not big so i created fresh logshipping for this database.

    Thanks to all of u for your valuable support.
    **Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79
    • Marked as answer by Raj_79 Wednesday, July 29, 2009 7:35 AM
    • Unmarked as answer by Raj_79 Thursday, July 30, 2009 6:20 AM
    Wednesday, July 29, 2009 7:33 AM

  • Again i got the same error today with other database, now when i tried to run the following statement

    RESTORE

     

    DATABASE DBName WITH RECOVERY

    it showing following error.

    Msg 4323, Level 16, State 1, Line 1

    A previous RESTORE WITH CONTINUE_AFTER_ERROR operation left the database in a potentially damaged state. To continue this RESTORE sequence, all further steps must include the CONTINUE_AFTER_ERROR option.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    Raj


    **Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79
    Thursday, July 30, 2009 6:19 AM
  • Raj were able to fix it, i am at same stage.

     

     

    Msg 4319, Level 16, State 4, Line 1

    A previous restore operation was interrupted and did not complete processing on file 'cxo_hfmstaging_dev'. Either restore the backup set that was interrupted or restart the restore sequence. 

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.


    Thanks

    Tuesday, March 30, 2010 10:03 PM
  • Had this some where in a DOC file, sorry no URL

    Refer to the SQL Server error log to find the root cause of the issue. It will give you a clear reason mentioning why the database is in Suspect mode. From SQL Server 2005 onwards, I recommend to use a filter while viewing the log and use "Database name" as the filter criteria. In this way you will only see the logs related to that particular database and then refer to the latest log/s for the root cause. Up until SQL Server 2000, the only option is reading the log from the latest entry backwards to find the root cause. Assuming you found the reason why database is in suspect mode, now you need to take appropriate step to fix the issue.

    Here I will discuss some of the possible issues which can put a database in Suspect mode and recommend appropriate resolution.

    Possibility 1: Some one deleted/misplaced a data/log file when SQL Server was offline and the database can not start because of missing file. The solution is to place the missing data/log file in proper location. The SQL Server Error Log error message will give you the exact name and path of the missing file. Once you place the file execute below command to bring your database online with no data loss.

    RESTORE DATABASE WITH RECOVERY

    Possibility 2: SQL Server could not access or place an exclusive lock on the data or log file while coming online. Typically I experience this when SQL Server is shared with some other tool (like Antivirus), which puts an exclusive lock on the data/log file. To resolve it, use process explorer and kill the file handler which placed lock on the file. You may want to involve your System Admins to get this step executed. Then execute below command and you will have your database online with no data loss:

    RESTORE DATABASE WITH RECOVERY

    Possibility 3: This is a worst case scenario. Database is in suspect because of a corrupted transaction. This is a bad news as you may have to lose data at this point unless you have a good backup! Also this is the most common case I saw for putting an OLTP database in Suspect mode.

    The root cause of this issue is actually from a guarantee taken by SQL Server to ensure transaction consistency under fundamental ACID property of RDBMS. The root cause of this issue is most likely SQL server abruptly went down/restarted in the middle of a transaction and while coming back, SQL server could not complete (commit/rollback) the transaction.

    At this point, I recommend you to take a decision. If you have a good backup and can restore the database in an acceptable time up to an acceptable point, then go ahead for it. But if restore is not an option at this point, then you have to execute below steps:

    Caution! Below steps will cause you to lose data and hence are extremely dangerous to execute. I recommend trying all other possible options including calling Microsoft Support before executing below steps.

    1. Switch the Emergency mode on for the database using below command:

    ALTER DATABASE SET EMERGENCY;

    2. Then execute below command:

    dbcc checkdb ('',repair_allow_data_loss)

    Please note that as stated above this is an extremely dangerous command to execute. It is a one- way command (that is you can not rollback back this execution once you started it) which can cause loss in data or database integrity. Technically, by executing this command you are actually authorizing SQL Server to force transactional recovery to run and skip the errors. If it faces errors, this operation scavenges as much out of the transaction log as it can and then rebuilds the transaction log from scratch. So taking this step is really the last resort you should try after every other attempts fails.

    After this operation is complete you will have your database back online. However, you'll most likely have lost a bunch of data, broken constraints and inherent business logic in the database but at least you haven't lost everything. You may want to involve your customers to run a sanity check on the data quality at this point.

    Possibility 4: If you find out that your data file is corrupted then most likely you have OS / Hardware level failure. For this these type of failure or anything really weird which you can not fixed easily with in acceptable amount of time, your best bet is restore from backup with out really wasting time in a "Code Red" situation.

    So in this article, I covered some possible reasons which can put a database in "Suspect" and then the options to be back in business in shortest possible time. To conclude, I would like to add below basic homework tips so that you can be in a good shape while handling this situation:

    1. Always ensure that you have a good backup/DR strategy and your customer is in agreement with the risk of possible data/time loss in case of disaster
    2. Do not ever attempt to "detach" a database which is in Suspect. This will do nothing but you will probably loose your ability to repair it.
    3. Read and understand the error log before taking any action and do not panic. Nothing in computer science is "unexplainable" so if you keep your calm and apply intelligence, you will come out of any situation with honor.

    Well thats a really helping things to bring the Database Online. But before going to perform the thrid step I would like to go for DBCC DBRECOVER(DBNAME) command. Also after setting the Database in emergency do not forget to execute the DBCC Checkdb command, this will give what are the objects or transcations having the problem. Then in the end go for DBCC CHECKDB with allowloss option. 

    Another possible issue/solution, probably under possibility 1:

    I get a lot of SAN detatch errors from a certain iSCSI SAN. The SAN Drives will drop, and reattach. Fortunately, the Windows OS sees the drives as being back but once SQL Server looses the connection, there is no reconnect. These will show up as suspect databases (multiple). Solution so far (and I have been lucky) is simply restart the SQL Service. Usually shows up as a 9002 error in the error log.

    Sorry, but I am going to completely, 100% disagree with you.

    RESTORE DATABASE <DB Name> WITH RECOVERY is what you run if your database is in a RESTORING state because an earlier backup was restored with NORECOVERY. It is not used to bring a database out of the suspect state.

    I did a quick test. Created a DB, shut SQL down, renamed the data file and restarted SQL. The DB came up in the RECOVERY PENDING state (not suspect). I then followed your instructions, renamed the file correctly and ran the RESTORE DATBASE statement.

    RESTORE DATABASE SuspectDB WITH RECOVERY

    Msg 3148, Level 16, State 3, Line 1
    This RESTORE statement is invalid in the current context. The 'Recover Data Only' option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline state filegroups cannot be specified.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.


    The correct solution, when a file has been moved or was locked by some other app when SQL started, is to rectify the root cause first (put the file back, remove whatever was locking it), then take the DB offline and bring it back online (or restart the SQL Service). That will allow SQL to open the DB again, this time find all the files and run the restart_recovery again this successfully.

    This will work if the DB state is RECOVERY PENDING (meaning that restart-recovery has not started due to missing or inaccessible files). If the DB state is truly suspect, it means there's a problem in the data file or log file, the restart recovery started (so all files are accessible) but could not complete. That's a lot more severe than just a missing file and usually indicates corruption of the data/log that was encountered during the restart-recovery.

    One additional note on Emergency mode repair. It's a last resort. If it fails, there's nothing else that can be done.

    Sorry Guys and Gals but I couldn't stay quiet over this.
    1. If you move Data files when in 'off line' mode it should have been communicated to your stakeholders that you were doing it and "Code red" irrelevant. “Some one deleted/misplaced a data/log file when SQL Server was offline and the database can not start because of missing file” just makes me angry where is your change control and why didn’t you backup the Database before taking it off line.
    2. “SQL Server could not access or place an exclusive lock on the data or log file while coming online. Typically I experience this when SQL Server is shared with some other tool” WTF. What other tool should be accessing your Datafiles apart from SQL Server and related services. And you have Anti-virus checking your data files, heaven help us all !!
    3. Database is in suspect because of a corrupted transaction. “The root cause of this issue is most likely SQL server abruptly went down/restarted in the middle of a transaction and while coming back” Now unless the undo and redo phase of the SQL server recovery fails you have bigger problems than a dodgy transaction and I hope you have a better backup strategy..
    And the comment regarding the use of DBCC commands “I recommend trying all other possible options including calling Microsoft Support before executing below steps.” Read books online. Have you tried ringing microsoft support ??
    4. I happen to agree on the probable cause, in theory, in practice anything this important to cause a “Code Red” would be on a highly available server or other enrolled in one of several scenarios the prevent this from occurring.

    And as foot note: Following, or at least recognizing, best practices and change control this type of situation should be a rare occurance. I have been doing this for a Long, Long time and suspect databases are RARE !

     

     


    yup
    Wednesday, March 31, 2010 8:14 AM
  • I suggest you to opan case with css and let them investigate the real issue. If u keep doing recovery of the databases you will be in trouble as you dont know what you are loosing.

     

     


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Wednesday, March 31, 2010 11:47 AM