I figured out a few things
select * from sys.dm_exec_query_stats
will show all the cached query plans. Unfortunately, no SQL text is shown there.
However, you can join the SQL text to the plans like so:
select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
From here it's pretty trivial to add a WHERE
clause to find the SQL I know is in the query, and then I can execute:
DBCC FREEPROCCACHE (plan_handle_id_goes_here)
to remove each query plan from the query plan cache. Not exactly easy or convenient, but it appears to work..
edit: dumping the entire query cache will also work, and is less dangerous than it sounds, at least in my experience:
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;
Rather than creating an alert for just this one item you should consider setting up Nagios or similar. You can then have it monitor just about anything you like and alert you when something is outside your predefined parameters. The relatively small amount of time needed to set it up will be more than repaid by freeing you from having to manually monitor and check things.
Best Answer
You associate an SQL Agent "alert" to detect the error, which then sends an email to an "operator" or runs a job.
You use
sp_add_alert
but the main info is here: Monitoring and Responding to Events