Why would the TrackedMessages_Copy_BizTalkMsgBoxDb start failing with "Query processor could not produce a query plan"?

Question

Why would the TrackedMessages_Copy_BizTalkMsgBoxDb SQL Agent job start failing with "Query processor could not produce a query plan"?

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN. [SQLSTATE 42000] (Error 8622).

Our SQL guys are talking about amending the stored proc. but we've told them to treat BizTalk db's as a black box

Answer 1

Did you try updating the statistics on the database table referenced by the stored procedure (which is run by the SQL Server Agent job? The query planner uses those to decide how best to execute your SQL.

Answer 2

It should go without saying, but before anything, make sure to backup your databases. In fact, if your regular backup jobs are running, you may be able to restore a backup and compare things to when it was working on this server. That said -

1. Check the SQL Agent Job to make sure no additional steps have been added/no plan has been forced/no hints are being used; it should just have one step called 'Purge' that calls the procedure below with the DB server and DTA database name as parameters.
2. Check the procedure (BizTalkMsgBoxDb.dbo.bts_CopyTrackedMessagesToDTA) to make sure it hasn't been altered.

If this is a production or otherwise sensitive box, back up the DBs and restore them to a local dev environment before proceeding!

3. If this is not production, see if you can run the procedure (perhaps in a transaction that you rollback) directly in SSMS. See if you get any better errors. Add print statements to see if you can find out exactly where it's getting conflicting hints.
4. If the procedure won't run, consider freeing the procedure cache (DBCC FREEPROCCACHE) and seeing if the procedure will run.

If it runs in your dev environment from a backup, you may have to start looking at server/database settings. I can't think of which ones off the top of my head that would cause this error though.

For what it's worth, well intentioned DBAs break BizTalk frequently. They decide that an index is missing or not properly covering, or that security could be improved, or that the database should be treated like other databases they administer are treated. I've seen DBAs do really silly things to the BizTalk databases that get very hard to diagnose.