October 16, 2010 at 6:35 am
Dear friends ,
I was wondering about this situation.
After some search i have find out that many people has this problem.
worst one has a Db about 6 Tera Byte and...
Do you know why this kind of errors happening?
I mean Why SQLServer makes pages with errors like this?
For example :
Page (1:1000698) in database ID 65 is allocated in the SGAM (1:511233) and PFS (1:994824), but was not allocated in any IAM.
Best Regards,
Ashkan
October 16, 2010 at 6:40 am
SQL Server doesn't. Corruption is, in the vast majority of cases due to a faulty IO subsystem. That means that there's a problem somewhere in your drives, san controller, fibre switch, firmware, cables, etc.
If you want help fixing, run the following and post the fill and unedited results
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Take a look at this article. http://www.sqlservercentral.com/articles/65804/
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 16, 2010 at 8:43 am
Dear Gail,
Thanks for reply,
I have used that command and it returned about 3000 line of error code:
This is last lines.
Server: Msg 8948, Level 16, State 1, Line 1
Database error: Page (1:7335) is marked with the wrong type in PFS page (1:1). PFS status 0x70 expected 0x60.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:7335) with latch type SH. VerifyPageId failed.
CHECKDB found 1563 allocation errors and 0 consistency errors not associated with any single object.
CHECKDB found 1563 allocation errors and 0 consistency errors in database 'FAQ'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (FAQ ).
Then I used this commands:
ALTER DATABASE FAQ
SET single_user WITH ROLLBACK IMMEDIATE;
go
DBCC checkdb ('FAQ', repair_allow_data_loss);
go
it returned 500 line of report (some errors and some repaired text) with this line at the end;
CHECKDB found 1000 allocation errors and 0 consistency errors in database 'FAQ'.
CHECKDB fixed 500 allocation errors and 0 consistency errors in database 'FAQ'.
repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (FAQ repair_allow_data_loss).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
then i used :
ALTER DATABASE FAQ
SET single_user WITH ROLLBACK IMMEDIATE;
go
DBCC checkdb ('FAQ',repair_fast );
go
and it returned 500 line of report (some errors and some repaired text) with this line at the end(like before);
CHECKDB found 1000 allocation errors and 0 consistency errors in database 'FAQ'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (FAQ repair_fast).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
and the loop is continued.
what should i do now??!!?!?:(
Best Regards,
Ashkan
October 16, 2010 at 8:47 am
I think i should post a new topic for this error:(
Best Regards,
Ashkan
October 16, 2010 at 9:18 am
ashkan siroos (10/16/2010)
Dear Gail,Thanks for reply,
I have used that command and it returned about 3000 line of error code:
I need to see them ALL to give you any useful advice. Save the list in a text file, zip and attach to your post.
Then I used this commands:
ALTER DATABASE FAQ
SET single_user WITH ROLLBACK IMMEDIATE;
go
DBCC checkdb ('FAQ', repair_allow_data_loss);
go
it returned 500 line of report (some errors and some repaired text) with this line at the end;
*sigh* Repair is never the first resort for fixing corruption. Done is done I suppose, do you have any idea how much data the repair lost?
and the loop is continued.
what should i do now??!!?!?:(
Without seeing all the errors, I can't give you a definitive answer, but likely either you have an IO subsystem that's producing corruption faster than you can fix it, or you have something that's irreparable.
Do you have a clean backup of this database?
I think i should post a new topic for this error
I think you should not. No point having multiple posts for the same problem, one will just get locked.
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 16, 2010 at 10:07 am
Ok , i don't know how to upload text file here so i will paste it here:
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6836) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6836) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6837) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6837) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6838) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6838) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6839) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6839) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6840) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6840) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6841) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6841) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6842) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6842) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6843) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6843) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6844) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6844) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6845) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6845) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6846) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6846) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6847) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6847) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6848) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6848) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6849) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6849) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6850) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6850) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6851) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6851) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6852) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6852) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6853) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6853) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6854) in database ID 11 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'.
Server: Msg 8966, Level 16, State 1, Line 1
Could not read and latch page (1:6854) with latch type SH. VerifyPageId failed.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:6855) in database ID 11 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'.
Best Regards,
Ashkan
October 16, 2010 at 10:13 am
🙁 I cant paste it all:(
how can i upload a file here?
Best Regards,
Ashkan
October 16, 2010 at 10:16 am
When composing your post, look below, in the "Post Options" section. There is a section titled "Attachments", with a button on the right that says "Edit Attachments". Click that button. In the popup, click the "Browse" button, select the file to upload. Click the "Upload Attachments" button. Click the "Close Window" link.
Then, click on the "Post Reply" button.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 16, 2010 at 10:25 am
Zip your text file and then attach it.
Also, what's the output of the following?
SELECT @@version
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 17, 2010 at 12:18 am
I have attached the file and also the output of @@version is:
Microsoft SQL Server 2000 - 8.00.2055 (Intel X86) Dec 16 2008 19:46:53 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Best Regards,
Ashkan
October 17, 2010 at 3:02 am
I still need you to answer one question of mine:
GilaMonster (10/16/2010)
Do you have a clean backup of this database?
That means a backup that does not have this corruption.
p.s. Was that list that you posted from after the repairs ran or before?
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 17, 2010 at 4:15 am
🙁 no I don't have a clean back up.
And that report is the last report.
Best Regards,
Ashkan
October 20, 2010 at 6:14 am
help please:(
Best Regards,
Ashkan
October 20, 2010 at 11:43 pm
I had a chat with an expert on this. It's not good news.
If checkDB with repair allow data loss is not repairing these errors, it means that for some reason they are not repairable. Since you have no good backup, the only way that this can be fixed is if you script all the objects, export all the data that you can and recreate this as a new database.
You also might want to look into your integrity check and backup jobs as they are clearly not adequate.
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 20, 2010 at 11:51 pm
I'd also suggest that you do a detailed examination of your IO subsystem, check firmware versions, check and and all logs, look for anything out of place.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply