Checkdb Error

  • Hello,

    When I run checkdb, I got this error

    DBCC results for 'agent_band_details_ASH699'.

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

    IAM page (0:0) is pointed to by the previous pointer of IAM page (1:394336) object ID 229575856 index ID 4 but was not detected in the scan.

    There are 1242 rows in 12 pages for object 'agent_band_details_ASH699'.

    We deleted the agent_band_details_ASH699 table cause it was a temporary table for our project.

    but the error is still there but the table has changed. I Think checkdb is laughing at me, or just kidding me.

    DBCC results for 'agent'.

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

    IAM page (0:0) is pointed to by the previous pointer of IAM page (1:394336) object ID 229575856 index ID 4 but was not detected in the scan.

    There are 17297 rows in 394 pages for object 'agent'.

    Here another output with tableresults

    2576161IAM page (0:0) is pointed to by the previous pointer of IAM page (1:394336) object ID 229575856 index ID 4 but was not detected in the scan.repair_allow_data_loss072295758564000139433602

    The repairlevel is at repair_allow_data_loss,is this mean that I cannot repair it without loosing data ?

    version is:

    Microsoft SQL Server 2000 - 8.00.2187 (Intel X86)

    Mar 9 2006 11:38:51

    Copyright (c) 1988-2003 Microsoft Corporation

    Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    I want to know what is IAM page error, or meaning, I want to debug deeper as you can show me.

    Thanks for your time

  • Can you please run the following and post the full output. I prefer not to make recommendations on corruption problems without seeing the full list of problems.

    DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS

    Do you have a clean backup (one without corruption)?

    An IAM page is an Index Allocation Map. It indicates which extents are used by an index.

    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
  • Here the output result

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

    IAM page (0:0) is pointed to by the previous pointer of IAM page (1:394336) object ID 229575856 index ID 4 but was not detected in the scan.

    CHECKDB found 1 allocation errors and 0 consistency errors in table 'agent_activity' (object ID 229575856).

    CHECKDB found 1 allocation errors and 0 consistency errors in database 'AgencyModel'.

    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AgencyModel ).

    Yes I have a clean backup.

    But I want learn how to debug, interpret, analyze checkdb error output result.

    Thanks to take me by hand.

  • If you want to learn CheckDB, the number one resource is, of course, Paul Randal's blog - http://www.sqlskills.com/blogs/paul/

    You can also have a look at an article I wrote explaining some of the errors that checkDB will give back. http://www.sqlservercentral.com/articles/65804/

    As for this precise error...

    Iam pages are in a chain. Each IAM page for a index or table has a link to the IAM page before it and a link to the one after it. What you have here is a chain linkage error. An IAM page says that it's previous page is (0,0) which is not possible. The (0,0) refers to file_id 0, page_id 0. There is no file 0 because the file IDs start with 1 (for the mdf file) and, while page IDs do start at 0, page 0 for any file is the file header page.

    Most likely this is the result of IO system glitch, zeros were written over a small portion of the drive.

    IAM page (0:0) is pointed to by the previous pointer of IAM page (1:394336) object ID 229575856 index ID 4

    Page (1:394336) is the 'address' of the page with the error. File 1, page 394336. It's an IAM page for index 4 (a nonclustered index) on the table with an object ID of 229575856. You can use the object_name function to get the name of the table involved, but there's no need, it's identified on the next line - agent_activity.

    I believe that, in this case, the recommendation of repair_allow_data_loss is incorrect as Index_id 4 will be a nonclustered index. If you identify the index with that ID (using sysindexes) and then drop and recreate the index, the error should go away.

    You should also check for hardware or IO errors in the system event log, see if you can ID a root cause for this.

    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
  • Ohhh thanks

    I look at the sysindexes tables and see indexes, but the index id 4 is existing in the table sysindexes but when I look with the GUI I can't see it. it's why we got the error "not detected in the scan", it's my interpretation :).

    There are 34 indexes in sysindexes with name like '_WA_Sys_aac_proc_dt_0DAF0CB0', the first row indexid is 4.

    here is the indexid 4

    229575856104858560x00000000000040x00000000000017210000-100344090000x0000000000000000x3D003D0008001703000000000000000002000100000000000400010000000000AD01AD0008000000000000000000000000000900000000020000090000000100_WA_Sys_aac_proc_dt_0DAF0CB00x0400000007E2C700D29B00001D33480000000000690E01000000000062B20642FAFEAE3A0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000080000

  • hugues.gauthier (3/20/2009)


    I look at the sysindexes tables and see indexes, but the index id 4 is existing in the table sysindexes

    Nope. IndexIDs are only unique per table. There can be many, many indexes with an id of 4. Look for the row in sysindexes where id matches the table agent_activity and the indid is 4.

    So, in other words.

    SELECT name from sysindexes where id = 229575856 and indid = 4

    Check the defintion of that index, then drop it and recreate it.

    DROP INDEX agent_activity.<Index name>

    CREATE INDEX <Index name> ON agent_activity (< column list for index here > )

    but when I look with the GUI I can't see it. it's why we got the error "not detected in the scan", it's my interpretation :).

    No.

    I told you why there's the 'not detected in scan' error. The previous pointer of the IAM page points to a page that cannot exist.

    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
  • Gail - in this case you're wrong.

    These errors indicate that the first IAM page for an index does not have an entry in sysindexes. This commonly occurs when someone manually updates the system tables.

    Not sure whether dropping the index will help - as there's no link to the index from metadata. I'd definitely take a backup of the database before trying this.

    IIRC, repair in 2000 will fix the metadata entry to point to the index - but again, I haven't seen that code for a while so I'd try running repair on a restored copy of the database first.

    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

  • Paul Randal (3/21/2009)


    Gail - in this case you're wrong.

    These errors indicate that the first IAM page for an index does not have an entry in sysindexes. This commonly occurs when someone manually updates the system tables.

    How do you tell that from this error? It looks the same as other IAM errors I've seen before

    IAM page (4:1067530) is pointed to by the previous pointer of IAM page (3:1034788) object ID 1504515122 index ID 0 but was not detected in the scan.

    Is it the 0,0 page reference that's the clue?

    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
  • Because I wrote the code. The NULL page Id is a special case.

    It's also documented in MSDN as being the meaning in that special case - see http://msdn.microsoft.com/en-us/library/aa258785(SQL.80).aspx

    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

  • Paul Randal (3/22/2009)


    Because I wrote the code.

    We don't hear that very often :w00t:

    Thank you for the contribution and the urls.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Paul Randal (3/22/2009)


    Because I wrote the code.

    😀

    Maybe I should have asked how I could tell that from that error, not having your unique advantage. 😉

    It's also documented in MSDN as being the meaning in that special case - see http://msdn.microsoft.com/en-us/library/aa258785(SQL.80).aspx

    Ah, thanks.

    So, how would the error look in the case where an IO error has zeroed out part of a page, including the previous page pointer of an IAM page further along in the chain. Or is that not possible?

    I'm asking so that I can tell them apart in future cases.

    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
  • Yeah - forgot to put a smiley in there 🙂

    For a 2576 error, the only time the first IAM page ID will be NULL is when the code knows that the missing reference is from the sysindexes (or 2005/2008 equivalent) row.

    In the zerod-out-IAM-page case, there would be an 8946 error saying that the allocation bitmap page header is invalid, plus a 7965 error saying that the table/index cannot be processed because IAM pages are invalid. I'll do a quick blog post with some examples.

    The book chapter has every case explained...

    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

  • Paul Randal (3/22/2009)


    Yeah - forgot to put a smiley in there 🙂

    🙂 I was worried there for a moment

    For a 2576 error, the only time the first IAM page ID will be NULL is when the code knows that the missing reference is from the sysindexes (or 2005/2008 equivalent) row.

    In the zerod-out-IAM-page case, there would be an 8946 error saying that the allocation bitmap page header is invalid, plus a 7965 error saying that the table/index cannot be processed because IAM pages are invalid. I'll do a quick blog post with some examples.

    Makes sense. Thanks.

    The book chapter has every case explained...

    It's on my wishlist, not available here yet. Not uncommon, it usually takes a couple months for books to come here.

    One local book site says 'Expected Feb 2009' the other 'Expected April 2009' 🙁

    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
  • Here's the blog post IAM page corruption examples. The 8946 doesn't crop up after all - that's for things like GAMs and SGAMs. I've zipped up the corrupt databases in each case to allow you to play around.

    Have fun

    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

  • Paul, is there a way to run the with repair on a specific file group rather than the entire DB? I have a similiar issue but it is only on a filegroup that is 80 GB, my DB is 900 GB. I know there is a dbcc checkfilegroup but I didn't see any repair options listed for it.

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

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