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 123»»»

No catalog entry found for partition ID.... Expand / Collapse
Author
Message
Posted Friday, August 14, 2009 5:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 24, 2010 2:28 AM
Points: 18, Visits: 102
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.
Post #770808
Posted Friday, August 14, 2009 5:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 39,886, Visits: 36,233
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 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 #770819
Posted Friday, August 14, 2009 5:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 24, 2010 2:28 AM
Points: 18, Visits: 102
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).
Post #770821
Posted Friday, August 14, 2009 5:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 39,886, Visits: 36,233
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 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 #770825
Posted Friday, August 14, 2009 6:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Post #770827
Posted Friday, August 14, 2009 7:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 24, 2010 2:28 AM
Points: 18, Visits: 102
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!
Post #770911
Posted Friday, August 14, 2009 7:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Post #770926
Posted Friday, August 14, 2009 8:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 24, 2010 2:28 AM
Points: 18, Visits: 102
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.
Post #770939
Posted Friday, August 14, 2009 8:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 39,886, Visits: 36,233
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 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 #770943
Posted Friday, August 14, 2009 8:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Post #770957
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse