How do I add multiple exclusions whilst configuring an alert

ALPHONSODELARDALPHONSODELARD Posts: 13 Bronze 2
edited January 26, 2017 7:40AM in SQL Monitor 6
SImple question, can't find the answer in the online documentation though...

How do I add multiple exclusions whilst configuring an alert?

I have two long running queries on the same instance, I want to exclude them both from alerts.

Do I comma separate the values? Use an Or? Do I have to use quotes - if so, single or double?

Or do I just write a list?

Eg

"Stage.HelperFactStudentCourseSummaryFinal"
"HelperFactCourseItemActivitySource"
Tagged:

Comments

  • Adam WAdam W Posts: 111 Silver 3
    edited July 14, 2017 11:56AM
    Hi,

    To enter multiple regular expressions, type each separate expression on a new line.

    In Configuration > Alert settings > Long-running query you should see an embedded help icon next to the field that explains how to add exclusions. I've pasted it below (apologies if you already had this and were looking for something more specific).

    Excluding specific queries

    To stop this alert being raised for certain queries, enter regular expressions to match those queries you want to ignore. You can match against both the process name - that is, the SQL connection name - (in the first box) and any SQL statements contained within the query (in the second box). To enter multiple regular expressions, type each separate expression on a new line.

    Regular expression syntax

    All regular expressions are case-sensitive. Type the pattern of letters to find, or use the following special characters:

    . matches any single character (wildcard character)

    ^ matches the start of a string

    [ ] matches any of the characters contained within

    [a-z] or [0-9] matches any character in the specified range

    |matches any of the elements separated

    * matches the preceding character zero or more times

    ? matches the preceding character zero or once

    Note: this is not an exhaustive list of regular expression syntax. SQL Monitor supports all standard .NET regex characters.

    Tips
    White space is included; adding a space will change what the expression matches
    Special characters need to be escaped e.g . $ ^ { [ ( ) ] } * + ? |

    Examples

    [Bb]ackup matches any string that contains "Backup" or "backup"

    Backup|restore matches any string that contains "Backup" or "restore" (but not "backup" or "Restore")
  • Thanks Adam... I missed the help icon... :oops:
  • Did this solution work I have used the expressions but can't get exclude some of the DB backups I'm trying to ignore. I type [BACKUP DATABASE] but this does not work
  • Listing several object names in the "Exclude queries that contain SQL commands or objects matching the following regular expressions" section worked for me. In one database we have the following listed:
    (Directly copied - note no white space, commas etc, just separate lines for each expression).
    Stage.HelperFactStudentCourseSummaryFinal
    Stage.HelperFactCourseItemActivitySource
    Stage.HelperFactCourseItemActivityFinal
    Stage.HelperFactSubmissionFinal

    Maybe the square brackets need to be escaped?
    "Special characters need to be escaped e.g . $ ^ { [ ( ) ] } * + ? |"
  • I don't want to use square brackets, I just thought that is what you had to do. How would I ignore back ups?
  • Have you tried just the BACKUP DATABASE text without the brackets?
  • yep that was the first thing I did. this is the line of code. I use the Holengren process.

    : BACKUP DATABASE [Warehouse] TO DISK = N'\\Mdvip-backup2\SQLPRODBKUP\RPTSRPD01\Warehouse\FULL\RPTSRPD01_Warehouse_FULL_20170713_023310.bak' WITH CHECKSUM, COMPRESSION
  • Try with just BACKUP DATABASE. If that fails, wait for Redgate Support to help?
  • yeah I will have to. thanks for your help!!
  • Well that didn't work as I got the alert again last night during the back up....ughhh this is annoying. Now I think I see why the previous DBA had is set to 5 hours! lol
Sign In or Register to comment.