January 15, 2013 at 4:27 am
January 15, 2013 at 4:30 am
January 15, 2013 at 5:45 am
jitendra.padhiyar (1/15/2013)
The were trying to perform below delete operation:DELETE FROM PTPortalUser.PTJOBLOGS WHERE INSTANCEID < 614469
and got error message:
Msg 8908, Level 22, State 6, Line 1
Table error: Database ID 23, object ID 901578250, index ID 0. Chain linkage mismatch. (1:753306)->next = (1:753307), but (1:753307)->prev = (1:706408).
It seems its consistency error that Checkdb was not able to repair. Should I run checkdb again with All_ErrorMSGS ? to know whts going on ? OR any other way to cope with this error ? Indexid=0 seems cluster index issue..
As I asked earlier...
Now, please run this so I can see if there are any remaining errors
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2013 at 5:47 am
jitendra.padhiyar (1/15/2013)
Just googled error message and found this issue occurs because the SHRINK operation cannot remove a page from the SYSFILES1 system table on the primary data file of the database. And we can fix it by applying latest service pack of SQL Server 2000!!
Err, no it is not caused by shrink trying to remove a page from sysfiles1 and no you cannot fix it by applying a service pack.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2013 at 5:47 am
Jayanth_Kurup (1/15/2013)
@Gail , would it make sense now to add a clustered index or rebuild the clustered index ?
No, it will fail.
Are you able to access the table , can you create a copy of the table ?
No, it will fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2013 at 3:53 am
Hello Gilamonster,
I ran DBCC Checkdb as below:
DBCC CHECKDB (PTPortal) WITH NO_INFOMSGS, ALL_ERRORMSGS;
And got below error message:
Server: Msg 8937, Level 16, State 1, Line 1
Table error: Object ID 901578250, index ID 1. B-tree page (1:753306) has two parent nodes (1:4661), slot 143 and (1:609), slot 1.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 901578250, index ID 1. Index node page (1:609), slot 180 refers to child page (1:753307) and previous child (1:753306), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 901578250, index ID 1. Page (1:753308) is missing a reference from previous page (1:753307). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'PTPortalUser.PTJOBLOGS' (object ID 901578250).
CHECKDB found 0 allocation errors and 3 consistency errors in database 'PTPortal'.
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (PTPortal ).
Can I go with Rebuilding of indexes using DBCC REINDEX ? Or pls suggest me better way to cope with this error.
January 17, 2013 at 5:02 am
Put the DB into single user mode and run CheckDB(<database name>, repair_rebuild)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2013 at 12:04 am
Yesterday I tried to run checkdb with repair_rebuild option but fails due to log file full. I will try again after shrinking db log file.
@Gail: Is it possible to perform checkdb on specific tables only ? Or is it possible to perform re-indexing on specific table in SQL Server 2000 ?
January 18, 2013 at 2:56 am
jitendra.padhiyar (1/18/2013)
Yesterday I tried to run checkdb with repair_rebuild option but fails due to log file full. I will try again after shrinking db log file.
Shrinking a full log is like trying to move the contents of a 5 litre bucket of water in to a 2 litre bucket.
The log is full. ie, there is no free space in the log and it needs to grow. Shrinking releases free space to the OS. Shrinking's the exact opposite of what you need to do.
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 18, 2013 at 3:27 am
Hello Gail,
I think there is no index on PTPortal db..!!
I ran
SELECT 'dbcc dbreindex(''PTPortal.DBO.' +name+ ''')' FROM sysobjects and did not found those corrupted tables there...
Just can see that there are primary constraints defined on both the tables.
Can you gimme any other option to know whether there are any indexes declared on that table ?
If there are no indexes that why we got that index mismatch error in dbcc checkdb ?
January 18, 2013 at 4:00 am
PTPortal.PTPortalUser.PTJOBLOGS has a corruption in its clustered index. Take the DB into single user mode and run DBCC CheckDB(PTPortal, repair_rebuild)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2013 at 11:08 pm
Thanks a lot Gail...!!!
I completed all the activity with no error and this time took full backuk before doing anything.:-D. I handed over the system to check if there is any error left or any data loss, am just waiting clients reply.
Heartly Thanks to you and SQLSERVERCENTRAL which is Online\LIVE help for fresh DBA like me.
January 24, 2013 at 2:50 am
Gail...!! Kudos...!! Its working perfect now... Clients are very happy. Thnx a lot for your guidance again.:-)
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply