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:
- scripted out the non-clustered indexes
- dropped the non-clustered indexes
- rebuilt the clustered indexes
- 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.
David Wimbush 