December 30, 2008 at 12:30 pm
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."
December 30, 2008 at 1:04 pm
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."
December 30, 2008 at 1:36 pm
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
December 30, 2008 at 2:03 pm
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."
December 30, 2008 at 2:11 pm
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
December 30, 2008 at 2:31 pm
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."
December 30, 2008 at 2:34 pm
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
December 30, 2008 at 2:45 pm
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."
December 31, 2008 at 12:01 am
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
December 31, 2008 at 6:43 am
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."
January 3, 2009 at 7:15 pm
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."
January 15, 2009 at 11:18 am
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
January 15, 2009 at 11:27 am
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
January 15, 2009 at 1:08 pm
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
January 22, 2009 at 2:11 pm
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."
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply