This isn't really a blog, its more of a holding page for my domain (seems a shame not to have a page), if I know you then add me on either LinkedIn or Facebook (links are on the right), however if I don't know you then I won't add you!

Wednesday, 6 July 2016

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.


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.

Issue/Problem – Inserting records into SQL Server table doesn’t result in entries in table

When carrying out a simple insert into a table in a SQL server table the X number of records affected text is displayed, although a select * from the table immediately afterwards does not yield any results.

There isn’t necessarily any single answer to this, so look at the following areas;
  • Refine your insert statement to use some hard coded values in place of a SQL select statement to remove any complication and identify if the error lies in the select statement as opposed to the insert transaction.  
  • If the table is a high turnover table (i.e. one that is cleared down and then repopulated with large amounts of data) check to see if there are any auto number columns that will have hit their limit (i.e. the limit of a integer etc).
  • Check the database server itself to ensure there is sufficient room remaining on the drives that run both the operating system and store the database files, also check that autogrow is on.
  • Attempt to rebuild any indexes on the table (this was what resolved the issue for me)

The frustrating fact with the issue above is that the “X number of records affected” following the insert leads any SQL developer to assume the records have been inserted successfully.