All we need is an easy explanation of the problem, so here it is.
I am getting the following
SqlException on calling a stored procedure:
Attempt to fetch logical page (5:65424) in database 2 failed. It belongs to allocation unit 7349876362857938944 not to 4899918190390149120.
Message=”Attempt to fetch logical page (5:65424) in database 2 failed. It belongs to allocation unit 7349876362857938944 not to 4899918190390149120.
Source=”.Net SqlClient Data Provider”
What does this exception mean? Is there any resolution to the above issue?
Although the database referenced in the error above indicates tempdb, similar errors referencing Message 605, may be fixed using the answers below.
Msg 605, Level 21, State 3, Line 1
Attempt to fetch logical page (1:8687634) in database 7 failed. It belongs to allocation unit 72057594364821504 not to 72057594052476928.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
If the database ID indicated by the error message is
2, the database affected is tempdb. One method for fixing this type of corruption in tempdb is to simply restart the SQL Server instance. For database ID’s other than 2, follow the recommendations below.
You may need to restore from backup, however try this:
As a member of the sysadmin role, execute
DBCC PAGE (2, 5, 65424, 3); and look for the
Metadata: IndexId value.
- If it is
1(clustered index) you need to restore from backup.
- If it is not
1, you can just rebuild the non-clustered index.
Run the DBCC command, and let us know what you find. Check https://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/ for details about the
DBCC PAGE command
Your database is corrupted. Time to restore from a good backup. If you have no backups, is time to learn about The Importance of Testing your Recovery Plan. An investigation into the reliability of your hardware is also warranted, watch for warning and errors in the system logs.
The error indicates your database is corrupt. The good news is that database ID 2 indicates this is the tempdb database, so fixing this is easy – just restart the database instance.
Once you restart the instance, you should figure out why the corruption happened. Start by running
chkdsk on the drive that the problem file is on. Next you’ll need to look at the storage itself and see if there’s any problems there. Could be a physical disk issue, could be HBA or RAID card drivers, etc.
What you would hear from a lot of people is “the database is corrupt, you need to restore” without even recommending that you take some few mins to do some of the following;
review your logs, find out when the issue first occured, the error that preceeded that line, check out the database/table the page belongs to
In my case this error was preceeded by;
Unable to find index entry in index ID 9, of table 629577281, in
database ‘XYZ’. The indicated index is corrupt or there is a problem
with the current update plan
A workaround was to trace the index referred to and temporarily delete it.
i later found a this Microsoft link that describes my exact issue and offers a solution link
hope this helps someone else in future
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!)
In my case, truncating and re-populating data in the concerned tables was the solution.
Most probably the data inside tables was corrupted.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂