Monthly Archives: June 2015

An unexpected error

I recently had an overnight job fail with this error message:

Msg 8630, Level 17, State 70, Line 1
Internal Query Processor Error: The query processor encountered an unexpected error during execution (HRESULT = 0x80004005).

Not much to work with there! Luckily the script has lots of PRINTs in it so it was easy to find the statement in question. It was a fairly simple UPDATE from a SELECT, nothing too complicated. I ran it and it worked. No sign of a block or deadlock at the time it failed so I figured it was just a one-off. You don’t have time to explore every rabbit hole.

But the next night the error happened again. I managed to narrow it down to the SELECT statement but still no idea what was wrong. It took a few tries to get it to work this time. Very strange. Time to get to the bottom of this. Books Online had nothing. I searched and found various things about memory pressure and corrupt dates but none of that panned out. I ran DBCC CHECKDB. No errors.

Sometimes you just need to call for help so I contacted Microsoft support. It was very interesting: the questions they asked, the process of collecting lots of data, and the information they got from that data. They confirmed what I thought which was that the machine had plenty of disk space, memory and processor to spare so it looked like a data issue. They suggested rebuilding the indexes on the tables involved in the SELECT.

The tables weren’t that big so I:

  1. scripted out the non-clustered indexes
  2. dropped the non-clustered indexes
  3. rebuilt the clustered indexes
  4. re-created the non-clustered indexes

This did the trick so it’s worth trying if you get this error and can’t find any other cause.


Back in the saddle

I’ve been neglecting my blog for too long. Time to get it going again. This is my new blog but I’ve been doing this since 2009. You can find my earlier posts here:

New content will follow shortly and I’ll gradually copy my older posts over.