Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

errors found by DBCC CHECKDB Expand / Collapse
Author
Message
Posted Saturday, October 16, 2010 6:35 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 30, 2013 5:05 AM
Points: 94, Visits: 232
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.


Thanks and regards,
Ashkan
Post #1005710
Posted Saturday, October 16, 2010 6:40 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
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

Post #1005711
Posted Saturday, October 16, 2010 8:43 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 30, 2013 5:05 AM
Points: 94, Visits: 232
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??!!?!?


Thanks and regards,
Ashkan
Post #1005748
Posted Saturday, October 16, 2010 8:47 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 30, 2013 5:05 AM
Points: 94, Visits: 232
I think i should post a new topic for this error

Thanks and regards,
Ashkan
Post #1005750
Posted Saturday, October 16, 2010 9:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
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

Post #1005754
Posted Saturday, October 16, 2010 10:07 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 30, 2013 5:05 AM
Points: 94, Visits: 232
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'.


Thanks and regards,
Ashkan
Post #1005764
Posted Saturday, October 16, 2010 10:13 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 30, 2013 5:05 AM
Points: 94, Visits: 232
I cant paste it all
how can i upload a file here?


Thanks and regards,
Ashkan
Post #1005766
Posted Saturday, October 16, 2010 10:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 6,594, Visits: 8,878
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1005768
Posted Saturday, October 16, 2010 10:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
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

Post #1005770
Posted Sunday, October 17, 2010 12:18 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 30, 2013 5:05 AM
Points: 94, Visits: 232
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)


Thanks and regards,
Ashkan


  Post Attachments 
faqError.rar (9 views, 2.01 KB)
Post #1005825
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse