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
20 comments
-
I presume you've tried something like ^[Ww]omaster ?
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 -
I added ^[Ww]omaster ?. I previously had \bWomaster\b. I'm still receiving alerts. This is the full SQL process fragment. Thanks.
SELECT "dbo"."Womaster"."WO #" FROM "dbo"."Womaster" ORDER BY "dbo"."Womaster"."Date Entered" DESC -
I think I was wrong to suggest the ^ to be honest as that only matches characters at the start of the string.
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 -
So far, I have the following in the exclusion list. I continue to receive alerts for the long queries involving Womaster.
Database Mail
Womaster|"Womaster"
\bWomaster\b
RESTORE VERIFYONLY
SQLBackup
DatabaseMail90
FROM "dbo"."Womaster"
sp_readrequest
SELECT "dbo"."Womaster"."
^[Ww]omaster ? -
It looks to me like your Womaster queries should get excluded. I will do some investigation as this could be a bug. I'll post here when I know more.
Many thanks
Chris -
OK. I was able to exclude the Database Mail queries by adding the sp_readrequest entry, so this does work for that.
-
Sorry for the delay getting back on this one. I've been testing this internally and I'm pretty sure that there is a bug when multiple regex expressions are specified on separate lines. This has been raised as a bug internally and we'll look into fixing it for an upcoming release.
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 -
I following your instructions for putting the exclusions on one line. The area in SQL Monitor where you place the exclusions only will hold so many characters before it wraps to the next line. THis does appear to be working though. Maybe the use of the | between each exclusion item resolves this. Thanks.
-
The RegEx wrapping to the next line shouldn't matter because as it turns out we only support a single expression. So multiple words/phrases will need separating with the pipe character '|'.
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 -
I have a bug report regarding this.
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! -
Sire wrote:The settings were unchanged, and the exclude text was still "IndexOptimize|DatabaseBackup" (not multiple lines).
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 -
Using relase version 3780 also.
-
I recommend
http://www.ultrapico.com/Expresso.htm
for developing regular expressions in C# syntax (which is what SQL Monitor uses) -
The reg exp is clearly not the problem here. And btw, the best product is http://www.regexbuddy.com/
-
Are you able to post the SQL statements that you are trying to match? I'm guessing that these refer to stored procedures?Sire wrote: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).
Thanks
Chris -
Chris Spencer wrote:Sire wrote:Are you able to post the SQL statements that you are trying to match? I'm guessing that these refer to stored procedures?
I'm using the famous Ola Hallengren scripts (http://ola.hallengren.com/).
SQL process fragment that fails to match on the regexp "IndexOptimize|DatabaseBackup":-- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed SET QUOTED_IDENTIFIER OFF SET TEXTSIZE 4096 EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'C:\SqlData\Backup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 336, @CheckSum = 'Y'
-
I'm checking the IndexOptimize and DatabaseBackup scripts now. I'm finding that the IndexOptimize query is excluded but excluding the DatabaseBackup one is a bit more of a pain.
On running: EXECUTE [dbo].[DatabaseBackup] @Databases = 'RedGateMonitor', @... = 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 -
Chris Spencer wrote: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".
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.Chris Spencer wrote: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?
SQL Server 2008 Enterprise 64bit.
Executing from scheduled jobs.
Thanks for your help! -
Sire wrote:One could argue it shouldn't be necessary to know what subqueries are run by the sp.
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 -
I don't consider this an enhancement since it worked in the previous version.
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!
Add comment
Please sign in to leave a comment.