CHECKDB Errors
-
Hi,
I have a SQL Server 2000 with SP3 and one of the databases gave me following errors when i ran dbcc checkdb. I tried dbcc checkdb with repair_rebuild option and didn't help me. Microsoft says it happens in SP1 and needs to upgrade but I am already SP3. They said move databases drop all objects, create database again and move the data. I know that will work as I already tried on test server. But that would be the last thing anybody would like to do. Does anybody has any ideas about it. Thank you in advance and here are the errors:
[1] Database telephone_rpt: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 22426681344 owned by data record identified by RID = (1:24:0) id = 1 and indid = 1.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 22426681344 owned by data record identified by RID = (1:24:0) id = 1 and indid = 1.
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 73811361792 owned by data record identified by RID = (1:138:2) id = 50099219 and indid = 3.
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 73807495168 owned by data record identified by RID = (1:1869:1) id = 338100245 and indid = 11.
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 73808412672 owned by data record identified by RID = (1:1869:14) id = 370100359 and indid = 6.
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 73813590016 owned by data record identified by RID = (1:1893:12) id = 994102582 and indid = 12.
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 720896 owned by data record identified by RID = (1:28906:8) id = 96 and indid = 2.
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 73812017152 owned by data record identified by RID = (1:105392:8) id = 2005582183 and indid = 4.
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 73806708736 owned by data record identified by RID = (1:105392:21) id = 2085582468 and indid = 1.
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 73809854464 owned by data record identified by RID = (1:191600:2) id = 1826105546 and indid = 5.
[Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 2: Errors found in text ID 66085715968 owned by data record identified by RID = (1:199104:2) id = 1410104064 and indid = 3.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 2. The text, ntext, or image node at page (1:64), slot 7, text ID 262144 is referenced by page (1:24), slot 2, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 2. The text, ntext, or image node at page (1:64), slot 21, text ID 1048576 is referenced by page (1:24), slot 17, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 2. The text, ntext, or image node at page (1:68), slot 5, text ID 73809657856 is referenced by page (1:7853), slot 1, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 2. The text, ntext, or image node at page (1:7859), slot 6, text ID 66085847040 is referenced by page (1:199104), slot 4, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 2. The text, ntext, or image node at page (1:28914), slot 2, text ID 22426943488 is referenced by page (1:24), slot 3, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Too many errors found (201) for object ID 2. To see all error messages rerun the statement using "WITH ALL_ERRORMSGS".
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1 allocation errors and 321 consistency errors in table 'sysindexes' (object ID 2).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 1 allocation errors and 321 consistency errors in database 'Vision_tel'.
** Execution Time: 0 hrs, 2 mins, 27 secs **
-
Happened to me once.
Used checkdb with the repair options but it didn't help.
I continue running check db with the repair options, and after five times, it solved the problem, and repair the db.
Also check the object ids with error that checkdb returns. running SELECT OBJECT_NAME('ID'), you will know wich objects, have errors. Rebuilding only those, is better than re creating the entire db.
-
Seems that your page chaining is corrupt. the database can not find the items on the right spot on the pages. Try Reorg or Reindex ( if DBCC checkdb (repair) does not work). If this does not work use a DTS run to export ALL data to a new database to rebuild the pages ( and pointers to these pages.) Keep possible dataloss in mind !!
GKramer
The Netherlands
********************************************************
from BOL(2004);
Error 8929
Topic last updated -- January 2004Severity Level 16
Message Text
Object ID O_ID: Errors found in text ID TEXT_ID owned by data record identified by RID.Explanation
This message accompanies messages 8962 or 8963 (both of which indicate corruption in a text node) when the owner of the corrupt text node can be found.
Action
HARDWARE FAILURE
Run hardware diagnostics and correct any problems. Also examine the Microsoft® Windows NT® system and application logs and the SQL Server™ error log to see if the error occurred as the result of hardware failure. Fix any hardware related problems.
If you have persistent data corruption problems, try to swap out different hardware components to isolate the problem. Check to ensure that your system does not have write caching enabled on the disk controller. If you suspect this to be the problem, contact your hardware vendor.
Finally, you might find it beneficial to switch to a completely new hardware system, including reformatting the disk drives and reinstalling the operating system.
RESTORE FROM BACKUP
If the problem is not hardware related and a known clean backup is available, restore the database from the backup.
DBCC CHECKDB
If no clean backup is available, execute DBCC CHECKDB without a repair clause to determine the extent of the corruption. DBCC CHECKDB will recommend a repair clause to use. Then, execute DBCC CHECKDB with the appropriate repair clause to repair the corruption.
Caution If you are unsure what effect DBCC CHECKDB with a repair clause has on your data, contact your primary support provider before executing this statement.
Performing a repair will cause the text node to be deleted.
Caution This may result in data loss.
If running DBCC CHECKDB with one of the repair clauses does not correct the problem, contact your primary support provider.
See Also
-
I was wondering will dbcc checkdb raise an error that would abort a job? I would like to setup a script that will check all my DBs but want to aleart an operator if the job fails.
Thanks
Carl
-
If CheckDB fails, I'm not sure it will fail the job. You can use "with table results" and store the answers for a job to scan them later.
I'd BCP or DTS the data out, but I've had similar issues in the past and BCP would fail in the middle. You can work around this by exporting with an ORDER BY clause and working below and above the corruption.
PSS has some DBCC commands to pull out data page by page and recover it and most likely iwll have you rebuild the table and indexes after dropping it. Call them if you cannot lose some data as it's worth the $249
-
Thanks. Here is the script that finished. I'll have to give some credit to Andrew J. Kelly as I started with his script for CHECKDB but I wanted automated notification.
USE DBAstuff
SET NOCOUNT ON
DECLARE @DBName NVARCHAR(100)
DECLARE cur_DBs CURSOR STATIC LOCAL
FOR
SELECT Catalog_Name
FROM Information_Schema.Schemata
WHERE [Catalog_Name] IN ('MASTER','MODEL','MSDB','TEMPDB')
-- WHERE [Catalog_Name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')
OPEN cur_DBs
FETCH NEXT
FROM cur_DBs
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Executing CHECKDB of ' + @DBName
INSERT INTO DBAstuff.dbo.tblCHECKDB
EXEC ('DBCC CHECKDB (' + @DBName + ') WITH TABLERESULTS, NO_INFOMSGS')
FETCH NEXT FROM cur_DBs INTO @DBName
END
CLOSE cur_DBs
DEALLOCATE cur_DBs
IF (SELECT count(*) FROM DBAstuff.dbo.tblCHECKDB) > 0
BEGIN
DECLARE @sqlString varchar(8000)
SET @sqlString =
'SELECT LEFT(Name, 20) as DBname, LEFT(Error, 12) as Error, LEFT(Level, 6) as Level, LEFT(State,12) as State, Left(MessageText, 80) as MessageText
FROM DBAstuff.dbo.tblCHECKDB a
JOIN master.dbo.sysdatabases b on a.dbid = b.dbid'
EXEC master.dbo.xp_sendmail
@recipients = 'you@yourdomain.com',
-- @recipients = 'MSSQL Admins',
@query = @sqlString,
@subject = 'READ NOW - DBCC CHECKDB ERRORS!!!',
@message = 'To view detials use DBAstuff.dbo.tblCHECKDB. After compleation TRUNCATE TABLE DBAstuff.dbo.trblCHECKDB'
END
ELSE
BEGIN
EXEC master.dbo.xp_sendmail
@recipients = 'you@yourdomain.com',
@query = @sqlString,
@subject = 'SUCCESSFUL - DBCC CHECKDB',
@message = 'To view detials use DBAstuff.dbo.tblCHECKDB. After compleation TRUNCATE TABLE DBAstuff.dbo.trblCHECKDB'
END
Script to create the table
CREATE TABLE DBAstuff.dbo.tblCHECKDB
(
Error INT,
Level INT,
State INT,
MessageText VARCHAR(7000),
RepairLevel INT,
Status INT,
DbId INT,
Id INT,
IndId INT,
[File] INT,
Page INT,
Slot INT,
RefFile INT,
RefPage INT,
RefSlot INT,
Allocation INT
 
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply