October 3, 2011 at 5:21 pm
Hello guys, im in a bit of a bind:
A partitioned database got wiped and then block-restored (no other backup available), it's now attached but wont go online since the log files are gone (another disk, very gone).
Upon starting SQL service, the db went in suspect mode, so i put it in emergency & single_user and executed dbcc checkdb('dbname',repair_allow_data_loss).
After a few seconds i get the result that there is not enough of database data to begin restoring the log.
The database is again put in suspect-mode and i'm back where i started.
October 3, 2011 at 5:24 pm
Is the situation beyond all hope of repair? Any ideas?
October 4, 2011 at 1:08 am
dbcc checkdb('db_name') with estimateonly
returned:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
October 4, 2011 at 2:34 am
serr4no (10/3/2011)
Hello guys, im in a bit of a bind:A partitioned database got wiped and then block-restored (no other backup available), it's now attached but wont go online since the log files are gone (another disk, very gone).
Upon starting SQL service, the db went in suspect mode, so i put it in emergency & single_user and executed dbcc checkdb('dbname',repair_allow_data_loss).
After a few seconds i get the result that there is not enough of database data to begin restoring the log.
The database is again put in suspect-mode and i'm back where i started.
What did you find in the SQLServer log? Why did you run DBCC with repair_allow_data_loss?
I guess there's not much you can do now.
-- Gianluca Sartori
October 4, 2011 at 3:43 am
Gianluca Sartori (10/4/2011)
serr4no (10/3/2011)
Hello guys, im in a bit of a bind:A partitioned database got wiped and then block-restored (no other backup available), it's now attached but wont go online since the log files are gone (another disk, very gone).
Upon starting SQL service, the db went in suspect mode, so i put it in emergency & single_user and executed dbcc checkdb('dbname',repair_allow_data_loss).
After a few seconds i get the result that there is not enough of database data to begin restoring the log.
The database is again put in suspect-mode and i'm back where i started.
What did you find in the SQLServer log? Why did you run DBCC with repair_allow_data_loss?
I guess there's not much you can do now.
It was the recommended action to take since there is no log file and db wasnt cleanly shutdown.
I am able to restore the db to state before the DBCC if something indeed got changed in the seconds query ran.
October 4, 2011 at 3:46 am
Too much done with no details. We have to take this step by step.
What is the database state? Is it attached to SQL? If so, what's the exact state of the DB and what are the error messages (from the error log) relating to this DB?
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
October 4, 2011 at 5:39 am
Database is attached and in emergency mode (was in suspect right after sql-server started up), the tables can be listed but not selected from.
Nothing has been done except dbcc checkdb ('PM',repair_allow_data_loss) which stopped after a few seconds.
Properties cannot be viewed.
From the error log:
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:8743947; actual 0:0). It occurred during a read of page (1:8743947) in database ID 6 at offset 0x000010ad816000 in file 'F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\<dbname>.mdf'.
Just running DBCC CHECKDB('dbname') returned a lot of this:
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:2491104) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8998, Level 16, State 2, Line 1
Page errors on the GAM, SGAM, or PFS pages prevent allocation integrity checks in database ID 6 pages from (1:2491104) to (1:2499191). See other errors for cause.
Msg 8909, Level 16, State 1, Line 1
Database consists of 30+ files with a main .mdf and .ndf partitions.
Total size: 3,7 TB
October 4, 2011 at 6:06 am
Please run the following and post the full and complete results. Pieces of the results are not enough.
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
October 4, 2011 at 6:07 am
serr4no (10/4/2011)
Database consists of 30+ files with a main .mdf and .ndf partitions.Total size: 3,7 TB
I know that my comment is totally useless now, but, hell, why don't you have a backup?
Gail is probably the most knowledgeable member you can find on these forums when it comes to data corruption. If she can't help you, nobody can.
-- Gianluca Sartori
October 4, 2011 at 6:09 am
serr4no (10/4/2011)
From the error log:SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:8743947; actual 0:0). It occurred during a read of page (1:8743947) in database ID 6 at offset 0x000010ad816000 in file 'F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\<dbname>.mdf'.
Is that the only error in the log? If not, post all of them. I can't judge the situation based on a subset of errors.
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
October 4, 2011 at 6:10 am
Gianluca Sartori (10/4/2011)
Gail isprobablythe most knowledgeable member you can find on these forums when it comes to data corruption.
Not true. Paul Randal posts here from time to time.
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
October 4, 2011 at 6:13 am
GilaMonster (10/4/2011)
Please run the following and post the full and complete results. Pieces of the results are not enough.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Thank you:
October 4, 2011 at 6:15 am
serr4no (10/4/2011)
http://pastebin.com/3auHfWDq%5B/quote%5D
I don't know what that link is supposed to be, and I'm not going to an unknown site for unknown content. Post the results here, or if they're big zip the output and attach it to the thread.
That's the results of the command I asked for, not some other CheckDB? I have no desire to read through informational messages for a week. I just want all the errors.
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
October 4, 2011 at 6:15 am
GilaMonster (10/4/2011)
serr4no (10/4/2011)
From the error log:SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:8743947; actual 0:0). It occurred during a read of page (1:8743947) in database ID 6 at offset 0x000010ad816000 in file 'F:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\<dbname>.mdf'.
Is that the only error in the log? If not, post all of them. I can't judge the situation based on a subset of errors.
Yes, that's the only error in ERRLOG and windows Event log.
October 4, 2011 at 6:19 am
GilaMonster (10/4/2011)
serr4no (10/4/2011)
Thank you:http://pastebin.com/3auHfWDq%5B/quote%5D
I don't know what that link is supposed to be, and I'm not going to an unknown site for unknown content. Post the results here, or if they're big zip the output and attach it to the thread.
That's the results of the command I asked for, not some other CheckDB? I have no desire to read through informational messages for a week. I just want all the errors.
That link is the paste of the complete result of DBCC CHECKDB ('<dbname>') WITH NO_INFOMSGS, ALL_ERRORMSGS
I am including the results here as txt aswell.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply