Can't fix it - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Can't fix it

Author

Topic

Closet_Rambo
Starting Member

9 Posts

Posted - 2007-08-05 : 19:42:34

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

899 Posts

Posted - 2007-08-07 : 16:18:22

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

Closet_Rambo
Starting Member

9 Posts

Posted - 2007-08-07 : 20:06:59

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.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-08-07 : 20:21:29

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

Closet_Rambo
Starting Member

9 Posts

Posted - 2007-08-08 : 21:20:44

Here is a link to the errors from your command.

[url]http://www.mikienet.com/error.htm[/url]

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

Thanks.

Closet_Rambo
Starting Member

9 Posts

Posted - 2007-08-09 : 08:51:35

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.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2007-08-09 : 18:29:46

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

Closet_Rambo
Starting Member

9 Posts

Posted - 2007-08-10 : 07:48:13

I've been trying to figure out how to drop the index.
And then search for the dup.

Closet_Rambo
Starting Member

9 Posts

Posted - 2007-08-10 : 10:24:54

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?

pootle_flump

1064 Posts

Posted - 2007-08-10 : 11:22:48

[code]SELECT pk_col1, pk_col2
FROM myTable
GROUP BY pk_col1, pk_col2
HAVING COUNT(*) > 1[/code]There is all sorts of deduping info in the FAQ sticky in the New To SQL Server forum.

Closet_Rambo
Starting Member

9 Posts

Posted - 2007-08-10 : 16:59:51

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources