SQL Server Performance

DB consistency errors

Discussion in 'General DBA Questions' started by holyterror_1, Jan 13, 2004.

  1. holyterror_1 New Member

    Hi,
    I tried running the dbcc dbreindex command and i got this error message,

    ODBC: Msg 0, Level 19, State 1
    SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

    when i run dbcc checkdb, it says

    Server: Msg 7995, Level 16, State 3, Line 1
    Database 'dsActiveIpoh' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECK processing.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    and when i run sp_helpindex on that particular table, i get funny names coming up: ©§8, which does not appear in the sysindexes table. Help!!!



    Raymond
  2. Luis Martin Moderator

    What SQL and SP do you have?


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. Luis Martin Moderator

  4. holyterror_1 New Member

    sp3 and sql 2k ent ed

    Raymond
  5. holyterror_1 New Member

    i've already been there and also ran the script provided, but i still get the error message:

    Server: Msg 7995, Level 16, State 3, Line 1
    Database 'dsActiveIpoh' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECK processing.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    Raymond
  6. Luis Martin Moderator

    Try:

    SELECT SO.Name as 'Table', SI.name as 'Index'
    from sysobjects as SO
    join sysindexes as SI
    on SO.Id = SI.id
    where INDEXPROPERTY(OBJECT_ID(SO.Name), SI.name , 'IsHypothetical')= 1





    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  7. satya Moderator

    Use DBCC CHECKDB with REPAIR_REBUILD clause to resolve the inconsistencies.
    Refer to books online for more information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. holyterror_1 New Member

    i've run the script, what i got was:

    Table Index
    TRACKED_OBJECT_STATUS©§8
    TRACKED_OBJECT_STATUS©§8
    TRACKED_OBJECT_STATUS©§8
    TRACKED_OBJECT_STATUS©§8
    TRACKED_OBJECT_STATUS©§8
    TRACKED_OBJECT_STATUS©§8

    how do i delete them?

    Raymond
  9. holyterror_1 New Member

    hi satya,

    i tried the dbcc checkdb with repair_rebuild, i'm still getting this error message:

    Server: Msg 7995, Level 16, State 1, Line 1
    Database 'dsActiveIpoh' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKDB processing.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Raymond
  10. Luis Martin Moderator

    Use SQL Analyzer, tools, Index Manager.
    Go to table I see if that index is there.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  11. satya Moderator

    May test and try to apply SP3a and if possible export data using dts to another database and recreate freshly, then import data from exported database.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. holyterror_1 New Member

    i don't see those hypothetical indexes in the index manager.

    another thing that i've noticed is, when i use enterprise manager to generate script for the tables, if i do not include the scripting for primary keys, foreign keys, defaults and check constraints, the scripts gets generated. but if i do include this, then i get the error message again. could this be corruption at the system tables level?

    i tried applying sp3a, but because i'm running sql server on a cluster, which the second node was brought down (to move all the processors to one box), it says i need to have the second node up before i can apply sp3a. this would mean i have to remove the processors and plug them back into the second node. is there a quicker way to fix this?

    Raymond
  13. Luis Martin Moderator

    Try:

    DROP INDEX 'TRACKED_OBJECT_STATUS.©§8'



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  14. holyterror_1 New Member

    I've dropped the index but it still appears when i run this query:

    SELECT SO.name as 'Table', SI.name as 'Index'
    from sysobjects as SO
    join sysindexes as SI
    on SO.id = SI.id
    where INDEXPROPERTY(OBJECT_ID(SO.name), SI.name , 'IsHypothetical')= 1

    Raymond
  15. holyterror_1 New Member

    Could this corruption occur because there were two concurrent DBCC DBREINDEX commands running, but on two different tables?

    Raymond
  16. Luis Martin Moderator

    No.
    But if the table is in use by applications, may be.
    How about DBCC CHECKALLOC, same message?


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  17. holyterror_1 New Member

    i ran dbcc checkalloc, no error messages were generated... any other suggestions?

    Raymond
  18. Luis Martin Moderator

    I'm afraid you must dump the table and recreate it, but I suggest way for others oppinions.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  19. holyterror_1 New Member

    You mean deleting the table and recreating again?

    I can't delete the data in this table, even when i do a select top 1 *, i get this message:

    [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
    Server: Msg 11, Level 16, State 1, Line 0
    General network error. Check your network documentation.

    Connection Broken


    but if i select from another table, it's ok.
    ??



    Raymond
  20. Luis Martin Moderator

    What I mean is what Satya said:
    ...and if possible export data using dts to another database and recreate freshly, then import data from exported database.

    Other option is DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS , but may be some data will loss.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  21. Luis Martin Moderator

    Or, from BOL:

    DBCC CHECKTABLE
    Checks the integrity of the data, index, text, ntext, and image pages for the specified table or indexed view.

    Syntax
    DBCC CHECKTABLE
    ( 'table_name' | 'view_name'
    [ , NOINDEX
    | index_id
    | { REPAIR_ALLOW_DATA_LOSS
    | REPAIR_FAST
    | REPAIR_REBUILD }
    ]
    ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
    [ , [ TABLOCK ] ]
    [ , [ ESTIMATEONLY ] ]
    [ , [ PHYSICAL_ONLY ] ]
    }
    ]


    Arguments

    'table_name' | 'view_name'

    Is the table or indexed view for which to check data page integrity. Table or view names must conform to the rules for identifiers. For more information, see Using Identifiers.

    NOINDEX

    Specifies that nonclustered indexes for nonsystem tables should not be checked.

    REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD

    Specifies that DBCC CHECKTABLE repair the found errors. The database must be in single-user mode to use a repair option and can be one of the following.

    Value Description
    REPAIR_ALLOW_DATA_LOSS Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair may be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed, back up the database.
    REPAIR_FAST Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.
    REPAIR_REBUILD Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.


    index_id

    Is the index identification (ID) number for which to check data page integrity. If an index_id is specified, DBCC CHECKTABLE checks only that index.

    WITH

    Specifies options on the number of error messages returned, locks obtained, or estimating tempdb requirements. If neither ALL_ERRORMSGS nor NO_INFOMSGS is specified, Microsoft® SQL Server™ returns all error messages.

    ALL_ERRORMSGS

    Displays all error messages. If not specified, SQL Server displays a maximum of 200 error messages per table. Error messages are sorted by object ID.

    NO_INFOMSGS

    Suppresses all informational messages and the report of space used.

    TABLOCK

    Causes DBCC CHECKTABLE to obtain a shared table lock.

    ESTIMATE ONLY

    Displays the estimated amount of tempdb space needed to run DBCC CHECKTABLE with all of the other specified options.

    PHYSICAL_ONLY

    Limits the checking to the integrity of the physical structure of the page and record headers, and to the consistency between the pages' object ID and index ID and the allocation structures. Designed to provide a low overhead check of the physical consistency of the database, this check also detects torn pages and common hardware failures that can compromise a user's data. PHYSICAL_ONLY always implies NO_INFOMSGS and is not allowed with any of the repair options.

    Remarks
    DBCC CHECKTABLE performs a physical consistency check on tables and indexed views. The NOINDEX option, used only for backward compatibility, also applies to indexed views.

    For the specified table, DBCC CHECKTABLE checks that:

    Index and data pages are correctly linked.


    Indexes are in their proper sort order.


    Pointers are consistent.


    The data on each page is reasonable.


    Page offsets are reasonable.
    DBCC CHECKTABLE checks the linkages and sizes of text, ntext, and image pages for the specified table. However, DBCC CHECKTABLE does not verify the consistency of all the allocation structures in the database. Use DBCC CHECKALLOC to do this verification.

    DBCC CHECKTABLE does not acquire a table lock by default. Instead, it acquires a schema lock that prevents meta data changes but allows changes to the data. The DBCC statement collects information, then scans the log for any additional changes made, merging the two sets of information together to produce a consistent view of the data at the end of the scan.

    When the TABLOCK option is specified, DBCC CHECKTABLE acquires a shared table lock. This allows more detailed error messages for some classes of errors and minimizes the amount of tempdb space required by avoiding the use of transaction log data.

    To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

    By default, DBCC CHECKTABLE performs parallel checking of objects. The degree of parallelism is determined automatically by the query processor. The maximum degree of parallelism is configured in the same manner as that of parallel queries. Use the sp_configure system stored procedure to restrict the maximum number of processors available for DBCC checking. For more information, see max degree of parallelism Option.

    Parallel checking can be disabled by using trace flag 2528. For more information, see Trace Flags.

    Result Sets
    DBCC CHECKTABLE returns this result set (same result set is returned if you specify only the table name or if you provide any of the options); this example specifies the authors table in the pubs database (values may vary):

    DBCC results for 'authors'.
    There are 23 rows in 1 pages for object 'authors'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    DBCC CHECKTABLE returns this result set when the ESTIMATEONLY option is specified.

    Estimated TEMPDB space needed for CHECKTABLES (KB)
    --------------------------------------------------
    2

    (1 row(s) affected)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Permissions
    DBCC CHECKTABLE permissions default to members of the sysadmin fixed server role or the db_owner fixed database role, or the table owner, and are not transferable.



    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  22. holyterror_1 New Member

    I can't even run this command on the table... i've tried this already. i'm getting this message:

    Server: Msg 7995, Level 16, State 3, Line 1
    Database 'dsActiveIpoh' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECK processing.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.



    if can't run a select * from the table in Query Analyzer, eill dts be able to export the data then?
    help!!!

    Raymond
  23. Luis Martin Moderator

    I really don't know, but try with DTS.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  24. satya Moderator

Share This Page