none
Sql server 2000& 2005 getting Database Suspect mode frequently

    Question

  • Hi,

      This is Manu here.

      i need a clarification on SQL server DB

      we had a application Database .The app which is developed by vb.net and the backend is sql 2000,2005 and 2008,2k8R2

      where the database goes suspect mode frequently.(there is no ub normal shutdown in server)so let  me know there is any option to        overcome this problem.

    Thanks&Regards,

    Manu

     


    MANU

    Tuesday, June 25, 2013 9:53 AM

Answers

  • Manu,

    Message looks clean except for this ''dbo.DMS_OPEN_VALUE, _dta_index_DMS_OPEN_VALUE_6_174623665__K6_K5_K7_K2_K1_K3_4_8_9_10' .Can you run DBCC CHECKDB (db_name ) for database which has ID=5 and post result here.I assume not sure this can be result of faulty hardware or some faulty HBA card ,also wud like to get ur storage conf,including HBA and controllers checked by SAN team.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by Manu021 Tuesday, July 02, 2013 6:06 AM
    Tuesday, July 02, 2013 5:33 AM

All replies

  • Hi Manu021,

    Thanks for your post.

    We discuss SQL Server Analysis Services related issue in this forum. For better support, I will move this thread to SQL Server Database Engine.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Thursday, June 27, 2013 6:02 AM
  • Some factors which can cause Databaseto go in suspect mode

    1. Rouge data coming from application

    2. Rouge OS

    3. Faulty harware system/oudated drivers for SAN.

    Now Can you post error message from SQL server errorlog(sp_readerrorlog) ,windows eventviewer for better inderstanding

    Were you able to run DBCC CHECKDB(DB_NAME) if so please post result here

    Restoring from latest clean backup is best method to recover from corrupt DB


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, June 27, 2013 6:31 AM
  • Below link may helps you:

    http://sql-server-recovery.blogspot.in/2010/10/repair-restore-sql-server-database-from.html

    Hi Pradeep,

    I would seriously not follow recommendations given in link provided by you.because

    1. It uses REPAIR_ALLOW_DATA_LOSS as primary and formost means to remove corruption which is seriuosly not advised .Without stressing on fact of restoring from clean backup.REPAIR_ALLOW_DATA_LOSS  will delte data from ur DB in order to repair it and there is no means you can indentify what is deleted unless u serach manually.

    2.If your DB is suspect mode it may be possible that(depends on corruption) you will not be able to run DBCC command.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Thursday, June 27, 2013 9:14 AM typo
    Thursday, June 27, 2013 9:14 AM
  • Check the SQL Server errorlog file for *why* it goes suspect. Then take it from there. Without knowing why (the error messages), we are totally blind.

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, June 27, 2013 12:57 PM
  • Thank for u r support

    MANU

    Friday, June 28, 2013 1:36 PM
  • Hi Tibor Karaszi,

     Herwith i have attached the error logs.....thanks for u r support  in advance.....waiting for u r earlier response......

    DBCC CHECKCATALOG (adv_tvs) WITH no_infomsgs executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.  

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


    DBCC CHECKALLOC (adv_tvs) WITH no_infomsgs executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 8 seconds.  

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


    DBCC CHECKALLOC (adv_tvs, noindex) WITH no_infomsgs executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    DBCC CHECKFILEGROUP is performing an exhaustive search of 2 indexes for possible inconsistencies.  This is an informational message only. No user action is required.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    Exhaustive search of 'dbo.DMS_OPEN_VALUE, _dta_index_DMS_OPEN_VALUE_6_174623665__K6_K5_K7_K2_K1_K3_4_8_9_10' (database ID 5) for inconsistencies completed.  Processed 1 of 2 total searches.  Elapsed time: 6953 milliseconds.  This is an informational message only. No user action is required.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.


    Exhaustive search of 'dbo.DMS_OPEN_VALUE, _dta_index_DMS_OPEN_VALUE_6_174623665__K1_K5_K6_K2_K7_11' (database ID 5) for inconsistencies completed.  Processed 2 of 2 total searches.  Elapsed time: 5843 milliseconds.  This is an informational message only. No user action is required.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

    Regards,

    Manu


    MANU

    Tuesday, July 02, 2013 5:22 AM
  • Manu,

    Message looks clean except for this ''dbo.DMS_OPEN_VALUE, _dta_index_DMS_OPEN_VALUE_6_174623665__K6_K5_K7_K2_K1_K3_4_8_9_10' .Can you run DBCC CHECKDB (db_name ) for database which has ID=5 and post result here.I assume not sure this can be result of faulty hardware or some faulty HBA card ,also wud like to get ur storage conf,including HBA and controllers checked by SAN team.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by Manu021 Tuesday, July 02, 2013 6:06 AM
    Tuesday, July 02, 2013 5:33 AM
  • Dear Shanky_621

    DBCC CHECKDB ADV_TVS (ID 5)

    DBCC results for 'DATASYNC_EXTRACT_HISTORY'.
    There are 6888 rows in 85 pages for object "DATASYNC_EXTRACT_HISTORY".
    DBCC results for 'sys.syscommittab'.
    There are 0 rows in 0 pages for object "sys.syscommittab".
    DBCC results for 'DMS_SPARE_PO'.
    There are 15143 rows in 303 pages for object "DMS_SPARE_PO".
    DBCC results for 'DMS_MODEL_JOB_TYPE'.
    There are 1796 rows in 31 pages for object "DMS_MODEL_JOB_TYPE".
    DBCC results for 'DMS_DLR_WAR_CLAIM'.
    There are 251 rows in 9 pages for object "DMS_DLR_WAR_CLAIM".
    DBCC results for 'DATASYNC_EXTRACT_ORDER'.
    There are 86 rows in 1 pages for object "DATASYNC_EXTRACT_ORDER".
    DBCC results for 'DMS_LANGUAGE'.
    There are 8 rows in 1 pages for object "DMS_LANGUAGE".
    DBCC results for 'DMS_SPARE_PO_DET'.
    There are 111032 rows in 2067 pages for object "DMS_SPARE_PO_DET".
    DBCC results for 'DMS_MODEL_PART'.
    There are 824 rows in 20 pages for object "DMS_MODEL_PART".
    DBCC results for 'DMS_SPARE_PO_DET_TEMP'.
    There are 0 rows in 1 pages for object "DMS_SPARE_PO_DET_TEMP".
    DBCC results for 'DMS_MODEL_SCHEME'.
    There are 0 rows in 0 pages for object "DMS_MODEL_SCHEME".
    DBCC results for 'DMS_DLR_WAR_CLAIM_TEMP'.
    There are 0 rows in 0 pages for object "DMS_DLR_WAR_CLAIM_TEMP".
    DBCC results for 'DATASYNC_FOLDERS'.
    There are 1 rows in 1 pages for object "DATASYNC_FOLDERS".
    DBCC results for 'DMS_EMP_LANGUAGE_PROFICIENCY'.
    There are 0 rows in 0 pages for object "DMS_EMP_LANGUAGE_PROFICIENCY".
    DBCC results for 'DMS_SPARE_PO_TEMP'.
    There are 0 rows in 1 pages for object "DMS_SPARE_PO_TEMP".
    DBCC results for 'DMS_MODEL_SPEC'.
    There are 1107 rows in 12 pages for object "DMS_MODEL_SPEC".
    DBCC results for 'DATASYNC_FROM_TO'.
    There are 26 rows in 1 pages for object "DATASYNC_FROM_TO".
    DBCC results for 'DMS_SPARE_POLICY_PLAN'.
    There are 128 rows in 2 pages for object "DMS_SPARE_POLICY_PLAN".
    DBCC results for 'DMS_MRP_DETAIL'.
    There are 14956 rows in 159 pages for object "DMS_MRP_DETAIL".
    CHECKDB found 0 allocation errors and 5 consistency errors in database 'adv_tvs'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (adv_tvs).
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    MANU

    Tuesday, July 02, 2013 7:33 AM
  • I didn't find the actual error messages in there. If you add the NO_INFOMSGS option, you will *only* get the errors, and no informational text. As in:

    DBCC CHECKDB(ADV_TVS) WITH NO_INFOMSGS

    However, since the finishing line say that "repair_allow_data_loss is the minimum repair level" it is likely that you should restore the database from your last known healthy backup. Just so you are mentally prepared...


    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, July 02, 2013 8:31 AM