Long-running Query Exclusions
cehottle
Posts: 38
I'm not that well versed in RegEx and I'm having a hard time contructing an expression to exclude jobs that have T-SQL containing the word Womaster or "Womaster". Has anyone used this yet and, if so, do you have an example of how this works? Thanks.
Comments
If you have and it's not working for you then I'll do some investigation to see if we have any issues in this area.
Thanks
Chris
Test Engineer
Red Gate
SELECT "dbo"."Womaster"."WO #" FROM "dbo"."Womaster" ORDER BY "dbo"."Womaster"."Date Entered" DESC
Have you tried just Womaster without any special characters. \b matches a backspace and I don't think that would work in your case.
[Ww]omaster should match both "Womaster" and "womaster"
Also the embedded help next to the regEx box has some useful tips and a link to a fairly comprehensive Microsoft regEx page.
Hope this helps.
Chris
Test Engineer
Red Gate
Database Mail
Womaster|"Womaster"
\bWomaster\b
RESTORE VERIFYONLY
SQLBackup
DatabaseMail90
FROM "dbo"."Womaster"
sp_readrequest
SELECT "dbo"."Womaster"."
^[Ww]omaster ?
Many thanks
Chris
Test Engineer
Red Gate
A workaround might be to make a one line regEx that incorporates all the text that you want excluded. I'm thinking something like this:
Database Mail|Womaster|RESTORE VERIFYONLY|SQLBackup|DatabaseMail90|sp_readrequest
This worked when I tried it on my test setup although mine only involved two different words.
Hope this helps
Chris
Test Engineer
Red Gate
We'll be changing the embedded help to reflect the functionality in the next version. However, it's possible that in a future version we will change it to work the way you expected it to.
Many Thanks
Chris
Test Engineer
Red Gate
When updating to the latest (beta) version about a week ago I started seeing long running query warnings that were excluded successfully in the previous version. The settings were unchanged, and the exclude text was still "IndexOptimize|DatabaseBackup" (not multiple lines).
I tried removing the text and entering it again.
Please check this and get back to me. Thanks!
That should work as it's the style of regular expressions I have been investigating on this thread. However, I've been using the released version (build 3780).
What build of SQL Monitor are you running by the way? The final beta build was 3529 so I'm presuming it's that one.
The released version of SQL Monitor can be found at:
http://www.red-gate.com/products/sql_monitor/index.htm
Thanks
Chris
Test Engineer
Red Gate
http://www.ultrapico.com/Expresso.htm
for developing regular expressions in C# syntax (which is what SQL Monitor uses)
Red Gate Software
Thanks
Chris
Test Engineer
Red Gate
I'm using the famous Ola Hallengren scripts (http://ola.hallengren.com/).
SQL process fragment that fails to match on the regexp "IndexOptimize|DatabaseBackup":
On running: EXECUTE [dbo].[DatabaseBackup] @Databases = 'RedGateMonitor', @Directory = N'E:\Backups', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 336, @CheckSum = 'Y'
I'm seeing a long-running query alert raised with a SQL process fragment like this:
BACKUP DATABASE [RedGateMonitor] TO DISK = N'E:\Backups\CSTEST$SQL2005\RedGateMonitor\FULL\CSTEST$SQL2005_RedGateMonitor_FULL_20101125_155854.bak' WITH CHECKSUM
There's nothing in this fragment that matches the "IndexOptimize|DatabaseBackup" regex but I'm finding that I can filter out this alert by using "IndexOptimize|BACKUP DATABASE|RESTORE VERIFYONLY".
Which version of SQL Server are you running these queries on out of interest? Also do you execute these scripts from within jobs or from directly from SSMS?
Thanks
Chris
Test Engineer
Red Gate
Thanks I will try this. I haven't been getting these warnings for several weeks before updating to the release version, so there might be a "breaking change" on your side? One could argue it shouldn't be necessary to know what subqueries are run by the sp.
SQL Server 2008 Enterprise 64bit.
Executing from scheduled jobs.
Thanks for your help!
Yes I agree it would be nice to be able to reliably exclude any sp by name. I think we might be limited here by the SQL Server stored procedure we use to get the query fragments.
I'll raise an enhancement request to ensure that this limitation gets investigated at some point.
Thanks
Chris
Test Engineer
Red Gate
I consider it a pretty serious bug, one which makes the software almost useless for me, since I get alerts all the time, and can't figure out the exact subquery that causes the alerts.
I hope you consider making this a priority. Thanks!