DB consistency errors | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DB consistency errors

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
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
May be there is hyphotetical indexs. Check: http://support.microsoft.com/default.aspx?scid=kb;en-us;818097 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
sp3 and sql 2k ent ed Raymond
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
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
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.
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
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
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
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.
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
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
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
Could this corruption occur because there were two concurrent DBCC DBREINDEX commands running, but on two different tables? Raymond
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
i ran dbcc checkalloc, no error messages were generated… any other suggestions? Raymond
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
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
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
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
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
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
May follow my response in this threadhttp://www.sqlmag.com/forums/messageview.cfm?catid=5&threadid=6205 and as I repeat to test and apply SP3a on this instance. 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.
]]>