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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s