SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Can't fix it
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Closet_Rambo
Starting Member

USA
9 Posts

Posted - 08/05/2007 :  19:42:34  Show Profile  Visit Closet_Rambo's Homepage  Reply with Quote

Hello,
I have two SQL servers running SQL 2000 SP4 in a Legato/EMC AAM cluster with Replistore.
Had a data corruption event.

========================================================================================

2007-07-24 07:45:35.81 spid56 Error: 823, Severity: 24, State: 2
2007-07-24 07:45:35.81 spid56 I/O error (torn page) detected during read at offset 0x000002d9916000 in file 'w:\sql\MSSQL\data\gu_trends_Data.MDF'..
2007-07-24 07:46:27.46 spid56 Error: 823, Severity: 24, State: 2
2007-07-24 07:46:27.46 spid56 I/O error (torn page) detected during read at offset 0x000002d9916000 in file 'w:\sql\MSSQL\data\gu_trends_Data.MDF'..
2007-07-24 07:46:34.64 spid56 Error: 823, Severity: 24, State: 2
2007-07-24 07:46:34.64 spid56 I/O error (torn page) detected during read at offset 0x000002d9916000 in file 'w:\sql\MSSQL\data\gu_trends_Data.MDF'..
2007-07-24 07:46:35.23 spid56 Error: 823, Severity: 24, State: 2
2007-07-24 07:46:35.23 spid56 I/O error (torn page) detected during read at offset 0x000002d9916000 in file 'w:\sql\MSSQL\data\gu_trends_Data.MDF'..
2007-07-24 07:46:35.48 spid56 Error: 823, Severity: 24, State: 2
2007-07-24 07:46:35.48 spid56 I/O error (torn page) detected during read at offset 0x000002d9916000 in file 'w:\sql\MSSQL\data\gu_trends_Data.MDF'..
2007-07-24 07:46:49.53 spid56 Error: 823, Severity: 24, State: 2
2007-07-24 07:46:49.53 spid56 I/O error (torn page) detected during read at offset 0x000002d9916000 in file 'w:\sql\MSSQL\data\gu_trends_Data.MDF'..
2007-07-24 07:46:50.09 spid56 Error: 823, Severity: 24, State: 2
2007-07-24 07:46:50.09 spid56 I/O error (torn page) detected during read at offset 0x000002d9916000 in file 'w:\sql\MSSQL\data\gu_trends_Data.MDF'..
2007-07-24 07:47:00.12 spid56 Error: 823, Severity: 24, State: 2
2007-07-24 07:47:00.12 spid56 I/O error (torn page) detected during read at offset 0x000002d9916000 in file 'w:\sql\MSSQL\data\gu_trends_Data.MDF'..
2007-07-24 07:47:00.70 spid56 Error: 823, Severity: 24, State: 2
2007-07-24 07:47:00.70 spid56 I/O error (torn page) detected during read at offset 0x000002d9916000 in file 'w:\sql\MSSQL\data\gu_trends_Data.MDF'..
2007-07-24 07:48:53.14 spid56 Error: 8908, Severity: 22, State: 6
2007-07-24 07:48:53.14 spid56 Table error: Database ID 10, object ID 581577110, index ID 0. Chain linkage mismatch. (1:1493509)->next = (1:1494150), but (1:1494150)->prev = (1:1493372)..
2007-07-24 07:49:19.34 spid56 Error: 8908, Severity: 22, State: 6
2007-07-24 07:49:19.34 spid56 Table error: Database ID 10, object ID 581577110, index ID 0. Chain linkage mismatch. (1:1493509)->next = (1:1494150), but (1:1494150)->prev = (1:1493372)..
2007-07-24 07:52:31.25 spid56 Error: 8908, Severity: 22, State: 6
2007-07-24 07:52:31.25 spid56 Table error: Database ID 10, object ID 581577110, index ID 0. Chain linkage mismatch. (1:1493509)->next = (1:1494150), but (1:1494150)->prev = (1:1493372)..
2007-07-24 07:56:53.39 spid56 Error: 8908, Severity: 22, State: 6
2007-07-24 07:56:53.39 spid56 Table error: Database ID 10, object ID 581577110, index ID 0. Chain linkage mismatch. (1:1493509)->next = (1:1494150), but (1:1494150)->prev = (1:1493372)..
2007-07-24 08:17:40.14 spid56 Error: 8908, Severity: 22, State: 6
2007-07-24 08:17:40.14 spid56 Table error: Database ID 10, object ID 581577110, index ID 0. Chain linkage mismatch. (1:1493509)->next = (1:1494150), but (1:1494150)->prev = (1:1493372)..
2007-07-24 08:55:42.93 spid59 Error: 15457, Severity: 0, State: 1
2007-07-24 08:55:42.93 spid59 Configuration option 'max text repl size (B)' changed from 65536 to 100000000. Run the RECONFIGURE statement to install..
2007-07-24 08:55:49.12 spid59 Error: 15457, Severity: 0, State: 1
2007-07-24 08:55:49.12 spid59 Configuration option 'max text repl size (B)' changed from 100000000 to 100000000. Run the RECONFIGURE statement to install..
2007-07-24 08:55:56.12 spid59 Error: 15457, Severity: 0, State: 1
2007-07-24 08:55:56.12 spid59 Configuration option 'max text repl size (B)' changed from 100000000 to 100000000. Run the RECONFIGURE statement to install..
2007-07-24 08:56:00.54 spid59 Error: 15457, Severity: 0, State: 1
2007-07-24 08:56:00.54 spid59 Configuration option 'max text repl size (B)' changed from 100000000 to 100000000. Run the RECONFIGURE statement to install..
2007-07-24 10:04:04.96 spid56 Error: 8908, Severity: 22, State: 6
2007-07-24 10:04:04.96 spid56 Table error: Database ID 10, object ID 581577110, index ID 0. Chain linkage mismatch. (1:1493509)->next = (1:1494150), but (1:1494150)->prev = (1:1493372)..
2007-07-24 10:07:45.82 spid59 Error: 8908, Severity: 22, State: 6
2007-07-24 10:07:45.82 spid59 Table error: Database ID 10, object ID 581577110, index ID 0. Chain linkage mismatch. (1:1493509)->next = (1:1494150), but (1:1494150)->prev = (1:1493372)..
2007-07-24 13:27:23.92 spid57 Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'.
2007-07-24 13:33:02.96 spid61 DBCC CHECKDB (GU_main) executed by ESERVER1\Administrator found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.
2007-07-24 13:56:43.31 server SQL Server terminating because of system shutdown.

================================================================================================

I have been trying to fix a copy of the DB on another server.
I tried DBCC CHECKTABLE with all rebuild options but it says "fix errors first".
I tried:
DBCC DBREINDEX (TRENDDATA, PK__TRENDDATA__286302EC)
and got:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '133'.
The statement has been terminated.

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/07/2007 :  16:18:22  Show Profile  Visit paulrandal's Homepage  Reply with Quote
Using REPAIR_REBUILD won't work because that level of repairs can't fix the torn-page - you need to use REPAIR_ALLOW_DATA_LOSS for that. Be aware that doing so will cause the page to be deleted - potentially losing data if the page is at the leaf level of your clustered index (which I suspect is the case).

Do you have a backup you can restore from to avoid data loss?


Paul Randal
Managing Director, SQLskills.com
Go to Top of Page

Closet_Rambo
Starting Member

USA
9 Posts

Posted - 08/07/2007 :  20:06:59  Show Profile  Visit Closet_Rambo's Homepage  Reply with Quote
Nope ... no backup.

I treid allow data loss and got no where.
I'll try it again.

Cant I just blow the index away and recreate it?

all I need are the numbers in the data.
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/07/2007 :  20:21:29  Show Profile  Visit paulrandal's Homepage  Reply with Quote
No - because I think the problem is with a leaf level page of the clustered index.

Can you post the output of

DBCC CHECKDB (GU_main, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

This should repair the problem (but delete the bad page). Btw - did you lose power at some point? Something happened to your hardware that caused the torn-page.

Paul Randal
Managing Director, SQLskills.com
Go to Top of Page

Closet_Rambo
Starting Member

USA
9 Posts

Posted - 08/08/2007 :  21:20:44  Show Profile  Visit Closet_Rambo's Homepage  Reply with Quote
Here is a link to the errors from your command.

http://www.mikienet.com/error.htm

The DB name is gu_trends and yes they probably killed the power trying to get the server back online.

Thanks.
Go to Top of Page

Closet_Rambo
Starting Member

USA
9 Posts

Posted - 08/09/2007 :  08:51:35  Show Profile  Visit Closet_Rambo's Homepage  Reply with Quote
DBCC CHECKDB with physical_only

returns:

DBCC results for 'GU_trends'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'GU_trends'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/09/2007 :  18:29:46  Show Profile  Visit paulrandal's Homepage  Reply with Quote
ok - what you need to do is drop the index, remove the duplicate row and then recreate the index. DBCC can't automatically delete duplicate rows for you - which one would it choose to get rid of? It can't without human intervention.

Thanks

Paul Randal
Managing Director, SQLskills.com
Go to Top of Page

Closet_Rambo
Starting Member

USA
9 Posts

Posted - 08/10/2007 :  07:48:13  Show Profile  Visit Closet_Rambo's Homepage  Reply with Quote
I've been trying to figure out how to drop the index.
And then search for the dup.
Go to Top of Page

Closet_Rambo
Starting Member

USA
9 Posts

Posted - 08/10/2007 :  10:24:54  Show Profile  Visit Closet_Rambo's Homepage  Reply with Quote
I got the index deleted with:

ALTER TABLE TRENDDATA DROP CONSTRAINT PK__TRENDDATA__286302EC

Now when I run:

CREATE UNIQUE CLUSTERED INDEX PK__TRENDDATA__286302EC ON TRENDDATA (SEQUENCENUMBER_, TID_)

I get a duplicate error (with "most significant primary key is 46644")
So now I need to find the duplicate.
How can I do this since there are two columns?

Below is part of a script, I exported from a new, clean, DB that defines the two indexes for table TRENDDATA:

ALTER TABLE TRENDDATA WITH NOCHECK ADD PRIMARY KEY CLUSTERED (TID_, SEQUENCENUMBER_)
GO
CREATE INDEX TIDDATE_IDX ON TRENDDATA(TID_, DATE_STAMP_)
GO

This should add the correct indexes that the APP expects but ...
I'm not sure about the "NOCHECK".
That can't be right.
That means don't check for duplicates right?

Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 08/10/2007 :  11:22:48  Show Profile  Reply with Quote
SELECT pk_col1, pk_col2
FROM myTable
GROUP BY pk_col1, pk_col2
HAVING COUNT(*) > 1
There is all sorts of deduping info in the FAQ sticky in the New To SQL Server forum.
Go to Top of Page

Closet_Rambo
Starting Member

USA
9 Posts

Posted - 08/10/2007 :  16:59:51  Show Profile  Visit Closet_Rambo's Homepage  Reply with Quote
Did this:

SELECT TID_, SEQUENCENUMBER_
FROM TRENDDATA
GROUP BY TID_, SEQUENCENUMBER_
HAVING COUNT(*) > 1
Order by TID_

but there are no dups displayed.
How do I get both rows to display?

Also how can I see all the other columns as well as these two?
There is a dat and value which I need to see.
This is the only way I can decide what to delete.

Thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000