Attempt to fetch logical page # in database failed. It belongs to allocation unit ....

What do you do when you get errors like below?
Attempt to fetch logical page () in database failed.
It belongs to allocation unit X not to Y.
Well, in most cases this error indicates to data
corruption issues in the database.
In one day, some of our SELECT queries started to fail
with this error in SQL Server 2008 R2 Production database.


Well, the first thing was to execute the DBCC CHECKTABLE and later DBCC CHECKDB.
Surprisingly, the DBCC CHECKTABLE and DBCC CHECKDB did NOT return any errors!
Also executing DBCC PAGE with the problematic page number returned data without any
errors. Also we executed the CHECKDB on Mirroring database and it also did not detect any errors.

The fact that the DBCC CHECKDB did not detect any errors was encouraging
from one side and not clear from second side.
Before trying to repair the corrupted page by restoring the corrupted page from the backup,
it was decided to restart the SQL Service, because all the symptoms were leading to thoughts
that the corruption was in memory and the on-disk image of the page is not corrupt.
Fortunately, in our case it helped. After the restart, the SELECTs against the problematic rows
run successfully.You also may try to clean the DB`s cache instead of restarting the SQL
Server.

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO

Bottom line, make sure you have a job that executes DBCC CHECKDB against your
production databases.