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.

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.

Answer/solution

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.

SSRS Error - There was an exception running the extensions specified in the config file. ---> Maximum request length exceeded

Hopefully this can help someone else out that has this issue, I had the error "Error 1 There was an exception running the extensions specified in the config file. ---> Maximum request length exceeded" displayed when publishing what I believed was a relatively straight forward SSRS report.

Upon googling most of the solutions tended to hint at it being related to a large mapping component being present, however this wasn't present in my report.

I then realised that the report I had written was done by copying an existing report as a starting point, that report had a few very large (file size wise) images, although I had deleted them from the report.  Looking in the Report Data > Images location I can still see them referenced in there, deleting them from there resolved the issue.