March 20, 2009 at 11:53 am
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
March 20, 2009 at 1:44 pm
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
March 20, 2009 at 1:58 pm
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.
March 20, 2009 at 2:24 pm
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
March 20, 2009 at 2:55 pm
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
March 20, 2009 at 3:28 pm
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
March 21, 2009 at 5:35 pm
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
March 22, 2009 at 1:59 am
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
March 22, 2009 at 4:54 am
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
March 22, 2009 at 6:11 am
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
March 22, 2009 at 6:48 am
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
March 22, 2009 at 7:11 am
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
March 22, 2009 at 7:33 am
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
March 22, 2009 at 8:14 am
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
September 7, 2010 at 7:41 am
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