Conistency Errors on SQL 7.0 DB

Question

Post reply

Conistency Errors on SQL 7.0 DB

The_SQL_DBA

SSChampion

Points: 10494
More actions
December 30, 2008 at 12:30 pm

#217036

There was a drive failure on this server, resulting in the loss of one data file. THERE WAS NO BACKUP for some reason. However, one the lost data file was recovered from the other SAN as the drives on this server were mirrored at the SAN level.

As a result of the failure, there were multiple databases that went into suspect mode, but I restored them from the backups. But this specific database could not be restored as there was no backup. Instead I detached the DB and attached with the files recovered from the other SAN. The DB is live now, but the DBCC CheckDB is throwing multiple consistency errors on this dabase. Also the DBCC CheckDB on master database came up with 1 Allocation error.

I am backing this database regularly now, but I believe the backups are corrupt too, as there was never a clean backup.

Also the inserts on one of the table fails, the reason given that Primary key violation, where as I am not specifying the PK in the insert as the table has an Identity key. Attached are errors from DBCC CheckTable...as the DBCC CheckDB takes forever to execute...also similar error were found on couple other tables..

I did a rebuild of all the indexes, but it did not fix any issues..

Let me know of the best solution with minimal data loss..

Thanks

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

I am posting the detailed error report.

When I run select * on the table I get the error as

Server: Msg 7105, Level 22, State 6, Line 1

Page (1:75439), slot 12 for text, ntext, or image node does not exist.

Connection Broken

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

From an initial read, without a backup there's no way of fixing this without a lot of data loss.

I'm no expert on SQL 7, I've never worked with SQL prior to SQL 2000. If it's not urgent and you can wait until the 5th Jan, Paul Randal should be back and can help you. Otherwise you may want to give CSS a call, though I don't know if they'll support a SQL 7 server.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 3

I will wait until Jan 5th probably..as I dont see any other options either. I have attached the detailed logs after running various commands. I ran these CheckDb commands by restoring a current backup of this SQL7.0 database on a SQL 2000 server. Also I assume most of the results are pretty accurate to my knowledge...will talk to my management and let them know if the data loss is acceptable if Paul also comes up with the only solution.

Gail: The last checkDB has some errors with indexes on the test database attachment 3(CheckDb after DatalossRepair.txt), what's up with that?

Thanks again for your help..would also like to hear from Paul on Jan 5th..

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

The_SQL_DBA (12/30/2008)

Gail: The last checkDB has some errors with indexes on the test database attachment 3(CheckDb after DatalossRepair.txt), what's up with that?

Repair is rather brute force. It just deallocates data pages. In doing so, it's caused some nonclustered indexes to have rows that no longer refer to data rows. You can either rebuild that index, or run repair again, but with the repair_rebuild option. Those errors will then go away.

That's giving you some indication what data the repair has dropped, so it's useful to read through to get an idea what you're going to lose.

Thanks again for your help..would also like to hear from Paul on Jan 5th..

He's on holiday until the 5th. No guarantees that he'll be visiting here that day.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 5

Don't now why I get this error when I try to do an insert on the table after the repair was run. The table has an identity key.

insert into tblTransactionResponse values(11011,'jnuk data')

The error is

Server: Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_tblTransactionResponse'. Cannot insert duplicate key in object 'tblTransactionResponse'.

The statement has been terminated.

But that field is not the primary key..is it something to do with the error I observed on the master db?

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Off hand, no idea. Is the pk on the identity? It could be that the identity seed is messed up.

Won't be due to master's problem. primary keys aren't kept in master.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 7

The primary key is on the Identity..which I am not referencing while doing the insert.

How do I reseed this field..if so will it fix the problem with the insert?

Thanks Again!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

DBCC CheckIdent should. I don't know if it's on SQL 7 though. You can use it to check what the current identity seed is, and change if necessary.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 9

The_SQL_DBA

SSChampion

Points: 10494

December 31, 2008 at 6:43 am

#919599

DBCC Checkident corrected the seed value on both test and the actual db..thanks a lot Gail..

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 10

Paul..I hope you are having fun during your holidays...I would like your insights on this issue once you are here please..thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Paul Randal One Orange Chip Points: 29544 More actions · Answer 11

Just got back a couple of days ago.

The PK errors are because repair takes no account of any constraints when it does repairs - it's just making the database physically consistent.

Can you try running repair again (on 2000)? On 2000, some of the errors would prevent deeper checks (and repairs) running and that may be why you've still got b-tree errors showing.

I admittedly only looked through the thread quickly - was there anything else to look at?

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 12

Paul Randal (1/15/2009)

I admittedly only looked through the thread quickly - was there anything else to look at?

He wanted to know if there were any options other than repair with data loss (no backup).

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul Randal One Orange Chip Points: 29544 More actions · Answer 13

Paul Randal

One Orange Chip

Points: 29544

January 15, 2009 at 1:08 pm

#927662

Fraid not.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 14

Thanks Paul for your insight, also thanks Gail for your suggestions.

Apparently I got rid of all the errors from CheckDB by running repair with allow data loss statement. Also there was corruption on some indexes, which had to be dropped and recreated.

But I did not understand the corruption in the master database, there was one errror in the sysproperties table, I got rid of it in the test run but haven't tried it on the actual database, please advice..

Server: Msg 8906, Level 16, State 1, Line 1

Page (1:10) in database ID 12 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.

CHECKDB found 1 allocation errors and 0 consistency errors in database

Paul, one question as a DBA what is the best way to avoid corruption in the first place..because on an OLTP system restoring from backup sometimes can mean more data loss than repair with allow data loss..i am optimistic of finding a safer approach to handle such issues at the same time honoring my SLA..;)

Thanks Again!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."