August 14, 2009 at 5:06 am
I'm getting this error (on a sql server 2005 database, using code which has been working fine on 2000)
I'm on SP3
Msg 608, Level 16, State 1, Procedure sp_myproc, Line 391
No catalog entry found for partition ID 72057594961920000 in database 2. The metadata is inconsistent. Run DBCC CHECKDB to check for a metadata corruption.
What does this mean? Can I create the catalog entry somehow? Running the dbcc command takes 25 mins and produces no warnings or errors.
The part of the Sp where the error occurs is something like:
insert into #temptable
select stuff from #temptable2
where....
I recently posted a plea for help and got an answer about #temptable2 - it was down to the difference between 2000 and 2005. But to make this problem simpler I've commented out reindexing on this temp table. It's a simple temp table which I'm creating in this proc, so I don't belive there is any metadata which can be inconsistent.
Where do I go from here?
---
Edit: I've found a possible cause and a workaround. It seems that doing this:
DBCC DBREINDEX('tempdb..#temptable2',' ',90)
works but makes the insert above fail. If I comment out that dbcc command, it's fine. Is it corrupting my index? Isn't that a bug?
I can't find anything conclusive which tells me whether that DBCC (or any other ones) are synchronous or asynchronous, so I put a minute's delay in and the problem still exists, so it would appear that I simply can't reindex temporary tables - something gets corrupted.
August 14, 2009 at 5:39 am
It's not a code problem, it's database corruption. Since it's in TempDB, you should be able to fix it by restarting the SQL service. That will cause TempDB to be recreated. There's no actual fix for this kind of corruption that I know of.
Just to be sure, can you run this on the model database please?
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
August 14, 2009 at 5:46 am
Thanks for your reply.
I've already stopped and started the server, then tried again, and it's made no difference.
The command:
DBCC CHECKDB (model ) WITH NO_INFOMSGS, ALL_ERRORMSGS
just produces:
Command(s) completed successfully.
I'm now running it on my database (and not model).
August 14, 2009 at 5:54 am
I'm not sure what could cause this, I have passed this on to an expert on corruption, don't know when I'll get a reply.
Can you try and run the checkDB on TempDB? I don't know if it's allowed on that DB or not, but is worth trying.
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
August 14, 2009 at 6:00 am
Gail pinged me on this as I don't have alerts on this forum.
This looks like a SQL Server bug - the DBCC DBREINDEX creates a new set of allocations for the temp table but the query plan for the insert isn't getting them during optimization. Clearly broken.
Can you try two things?
1) put a 'GO' between the DBCC DBREINDEX and the insert so they're not in the same batch - if it works now, that proves it's a bug.
2) try using an ALTER INDEX ... REBUILD and see if that makes a difference (shouldn't do) - also DBCC DBREINDEX is depracated in SQL 2005
The 608 is a generic message about corruption - DBCC CHECKDB in tempdb doesn't do very much at all as it can't create too much of a transactionally consistent view of the database - it definitely doesn't do consistency checks on temp tables.
Unrelated question: why are you rebuilding a temp table anyway? Is it really that big and having that much fragmentation? No reason it shouldn't work, just curious.
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
August 14, 2009 at 7:47 am
Thanks for you help with this!
CHECKDB on model, tempdb and my db all return no errors.
> put a 'GO' between the DBCC DBREINDEX and the insert so they're not in the same batch - if it works now, that proves it's a bug.
I don't have a trivial example I can do this on. That is, there is no problem to diagnose when I just run some code to create the temp table, reindex it then insert into it. And I can't put a GO into a SP as far as I know, so I can't try that approach on the SP which is failing.
> try using an ALTER INDEX ... REBUILD and see if that makes a difference (shouldn't do)
It didn't make a difference.
> Unrelated question: why are you rebuilding a temp table anyway? Is it really that big and having that much fragmentation? No reason it shouldn't work,
> just curious.
I didn't write the SP, but I imagine that you're right - that the table can potentially be quite big and it was considered beneficial to reindex it after the inserts and before it's used later in the same SP. It may be that it was coded this way defensively and that there wouldn't actually be a performance problem on the production server (obviously I'm not playing around on that server at the mo!).
SQL Server seems pretty solid, so I've never assumed any problem was caused by a bug, and not my ignorance or environmental factors, but this is looking pretty strange.
edit:
I create the index(es) like this:
CREATE INDEX z_TSheets_indx1 ON #TSheets ( EmpNo )
CREATE INDEX z_TSheets_indx2 ON #TSheets ( TSheetID, ActivityID )
If I create either of them, the reindex fails. The reindex doesn't fail if there are no indexes to reindex. I'm not sure this information helps!
August 14, 2009 at 7:56 am
ok - in that case if it only happens under load with concurrent executions of the SP, then I'd say it's definitely a bug. The index rebuild code is interacting with the new allocation code in tempdb (that my team wrote) that does temp table 'caching'. If you're really motivated, call into Product Support and tell them I think this is a bug. Get me an SR number and I'll help it get escalated past frontline support.
If you're not motivated, take out the DBREINDEX - I think it's completely superfluous.
Let me know which way you're going to go - I'll escalate directly to them dev team if you're not going to call support.
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
August 14, 2009 at 8:04 am
Paul Randal (8/14/2009)
ok - in that case if it only happens under load with concurrent executions of the SP, then I'd say it's definitely a bug. The index rebuild code is interacting with the new allocation code in tempdb (that my team wrote) that does temp table 'caching'. If you're really motivated, call into Product Support and tell them I think this is a bug. Get me an SR number and I'll help it get escalated past frontline support.If you're not motivated, take out the DBREINDEX - I think it's completely superfluous.
Let me know which way you're going to go - I'll escalate directly to them dev team if you're not going to call support.
Thanks!
Paul,
Thanks a lot for your help here. I'm motivated to get a result as I'm keen to improve my SQL skills and it would be great to know that this isn't my code (or code that I support) which is at fault. Having said that, I'll be away from work for a few weeks starting from a few hours from now and have to get stuff finished. I'm not sure who in my organization would be responsible for liaising with MS on this issue (possibly me, which would take yet more time I can't afford right now).
So if you were to escalate it yourself that would be great, and I've already bookmarked this page so I can keep an eye on any updates. And I'll try and produce a cut down SP which reproduces the problem without needing loads of other code/data which I can't republish.
Thanks again,
Alex.
August 14, 2009 at 8:06 am
alex (8/14/2009)
I didn't write the SP, but I imagine that you're right - that the table can potentially be quite big and it was considered beneficial to reindex it after the inserts and before it's used later in the same SP. It may be that it was coded this way defensively and that there wouldn't actually be a performance problem on the production server (obviously I'm not playing around on that server at the mo!).
Suggestion:
Change the proc so that the indexes are created after the insert is done. That way the reindex won't be needed and you might get around this problem.
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
August 14, 2009 at 8:13 am
Ok - I've started a thread with some folks from the Storage Engine, Query Processor, and Product Support. Will let you know what I find out.
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
August 14, 2009 at 8:58 am
Suggestion:
Change the proc so that the indexes are created after the insert is done. That way the reindex won't be needed and you might get around this problem.
Gail, thanks for your help here too - it's appreciated.
I was wrong. The indexes are added before the temp table is populated. Here's how it goes:
The temp table is created, and indexes added. There's a while loop. Stuff is added into the offending temp table. Then the temp table is reindexed (if you want the SP to fail, that is!), then the temp table is joined on as part of the update of another table. The temp table is truncated at the end of (but still inside) the loop.
So it's possible that the repeated reindexing and truncating is part of the problem. I'll try and have a play with this later.
August 14, 2009 at 9:06 am
How many entries are added to the temp table before the index rebuild? Are they added in index key order? Is there a noticeable difference in perf if the index rebuild is removed?
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
August 14, 2009 at 9:18 am
and, if you move the creation of the indexes to after the population, do you still get this error? If not, that's possibly a good workaround. It also means you're not getting the overhead of updating indexes while the table's been populated.
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
August 14, 2009 at 10:04 am
Consensus (without proving it yet) is that this looks like a bug. Do you want to push for a hotfix if that turns out to be the case? If so you'll need to call in to Product Support and they'll pick it up. if not, they'll at least investigate and confirm, and then get it into the next release.
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
August 14, 2009 at 2:20 pm
Ok - this has now been proven to be a bug inside MS, with a simple repro. Alex - let me know if you want to push for a hotfix for this and I'll give you the internal bug number that's been opened that you can use when you call PSS (and some high-level names to add to make sure you get the correct response).
Thanks for posting about this (and Gail, thanks for pulling me in).
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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply