This question is in regards to SQL 2005 and email alerts on long running queries.
Does this sound like the best approach (with the least impact to the server) to receiving alerts to long running queries is the following:
- Create a server side trace that monitors across all databases or a specific database for events 10 (RPC:Completed) and 12 (SQL:Completed).
- Set a trace filter on Duration for greater than n amount of time (for example, greater than 2 seconds).
- Separate stored procedure that runs every n seconds (for example 5 or 10) to check for new entries and sends out an email alert. (To detect new entries I would need to keep track of the last entry detected but that is trivial.)