Attempt to fetch logical page in database 2 failed. It belongs to allocation unit X not to Y

Question

Started to get following error when executing certain SP. Code related to this error is pretty simple, joining #temp table to real table

Full text of error:

Msg 605, Level 21, State 3, Procedure spSSRSRPTIncorrectRevenue, Line 123
Attempt to fetch logical page (1:558552) in database 2 failed. It belongs to allocation unit 2089673263876079616 not to 4179358581172469760.

Here is what I found: https://support.microsoft.com/en-us/kb/2015739

This suggests some kind of issue with database. I run DBCC CHECKDB on user database and on temp database - all passes.

Second thing I'm doing - trying to find which table those allocation units belong

SELECT au.allocation_unit_id, OBJECT_NAME(p.object_id) AS table_name, fg.name AS filegroup_name,
au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.filegroups AS fg ON fg.data_space_id = au.data_space_id
WHERE au.allocation_unit_id in(2089673263876079616, 4179358581172469760)
ORDER BY au.allocation_unit_id

This returns 2 objects in tempdb, not in user db. So, it makes me think it's some kind of data corruption in tempdb? I'm developer, not DBA. Any suggestions on what I should check next?

Also, when I run query above, how can I tell REAL object name that I understand? Like #myTempTable______... instead of #07C650CE

Answer 1

I was able to resolve this by clearing the SQL caches:

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO

Apparently restarting the SQL service would have had the same affect.

(via Made By SQL, reproduced here to help others!)

Answer 2

I have like your get errors too. firstly you must backing up to table or object for dont panic more after. I tryed below steps on my Database.

step 1: Backing up table (data movement to other table as manuel or vs..how can you do) I used to below codes to my table move other table

--CODE-

set nocount on;
DECLARE @Counter INT = 1;
DECLARE @LastRecord INT = 10000000; --your table_count

 WHILE @Counter < @LastRecord 
BEGIN 
BEGIN TRY  
    BEGIN
        insert into your_table_new SELECT * FROM your_table  WHERE your_column= @Counter --dont forget! create your_table_new before
    END 
END TRY

BEGIN CATCH    
     BEGIN
        insert into error_code select @Counter,'error_number' --dont forget the create error_code table before.
     END
 END CATCH
SET @Counter += 1;
END;

step 2:

-DBCC CHECKTABLE(your_table , REPAIR_REBUILD )
 GO

check your table. if you have an error go to other step_3.

step 3: !!attention!! you can lost some data/datas on your table. but dont worry. so you backed-up your table in step_1.

-DBCC CHECKTABLE(your_table , REPAIR_ALLOW_DATA_LOSS)
GO

Good luck! ~~pektas