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!