Configuring long running query alert

krmarks

Has anyone been able to configure the long running query alert to your liking? I have a server that I just can't seem to configure. I am running version 7.1.6.9196 and whenever I look at the long running query alert, I only see "The underlying data for this alert has been purged." when I look at the details of the alert (note: I have data purging set for after 2 weeks, so the data should be there...and it doesn't show up when I enable the trace either). That pretty much leaves me guessing as to what query redgate thinks is the issue. So to help troubleshoot it, I set the alert to anything running 2 DAYS in length and I was still getting hundreds of alerts each day. When I look at active spids or even just connected spids, nothing is running even close to that long, other than that we have mirroring set up on the server.

Can anyone help me configure the alert or point me in the right direction?

Here is what I have in place for the regex:
Exclude queries with a SQL process name matching the following regular expressions:
[Red Gate Software - SQL Tools]
[SQLAgent - TSQL JobStep]
Microsoft SQL Server Management Studio - Query
Microsoft SQL Server Management Studio

Exclude queries that contain SQL commands or objects matching the following regular expressions:
WAITFOR
DB MIRROR
AWAITING COMMAND
[SELECT INTO]
[INSERT INTO]
[INSERT INTO dbo.dba_indexDefragStatus]

Russell D

I would advise upgrading and retesting this behaviour. We had an issue in 7.1.6 where we were picking up an internal SQL query, this was fixed in 7.1.9.