Issue/Problem – Error when running a select/insert into table SQL server (Attempt to fetch logical page (1:28791) in database 12 failed. It belongs to allocation unit X not to Y.)

When running a SQL server agent job that carries out a large insert (64k records), the job fails after attempting to insert the records with the following error text.

Attempt to fetch logical page (1:28791) in database 12 failed. It belongs to allocation unit X not to Y.

This error also occurred when querying the table.

Answer/solution

This is related to issues regarding the storage location of the table being mismatched, SQL gurus more experienced than me will understand this further.  However as this example was a table that was cleared down and repopulated from another data source, there is not data loss implications.  The priority was to get the table populated with data so that the other related systems could continue functioning.

This was done by right clicking on the table and click Script Table as > CREATE to > New query window.  Then to rename the table with the prefix old (so that I could debug/investigate it later) and then run the script generated as the create table, then rerun the insert script and test with a select.

Obviously the above does not solve the root cause and doesn’t work if the table concerned contains data native to the database that cannot be replaced/repopulated.