locked
Adding new alert with sp_add_alert don't work RRS feed

  • Question

  • Hello,

    when I try to add an alert with sp_add_alert like this:

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_alert @name=N'Test', 
    		@enabled=1, 
    		@delay_between_responses=0, 
    		@include_event_description_in=0, 
    		@performance_condition=N'Replication Dist.|Dist:Delivered Cmds/sec|fancyReplicationname|>|1000', 
    		@job_id=N'00000000-0000-0000-0000-000000000000'
    GO
    

    I get this error-message:

    A performance condition must be formatted as:
    ‘object_name|counter_name|instance_name|comparator( or < or =)|numeric value'.

    Did you recognize the missing "greater than" ?

    I tried to add warning with < and =, but the Error-Message is the same... So it cannot be the "greater than".

    I already checked the "performance-condition" with this

    msdb.sys.sp_helptext 'dbo.sp_verify_performance_condition'

    The interesting Like looks like this:

      IF (PATINDEX(N'%_|%_|%|[><=]|[0-9]%', @performance_condition) = 0)
    

    So, there is not the problem.

    Why can't I add my custom warning? 

    Best Regards

    Björn

    PS:

    I'm using SQL Server 2014 Enterprise Core on a Windows Server 2008R2 Enterprise

    Thursday, July 17, 2014 8:05 AM

Answers

  • Hi Björn,

    As you know, SQL Server extracts object name, counter name, instance name, operator and value from the @performance_condition, and then adds the alert. Before adding the alert, we need to verify the object name, counter name, instance name and the operator are correct by using sp_verify_performance_condition.
    The steps to verify the @performance_condition are:

    1. Verify the format is correct by using regular expression(as you commented)
    2. Get the position of the first ":"
    3. Start from the position got from step 2, extracts object name, counter name, instance name by delimiter "|". During the extraction, recording the number of delimiter
    4. Check whether the count of delimiter is 4. If not, raise error.
    5. Check whether the extracted names are existing in dbo.sysalerts_performance_counters_view

    In this case, the issue happened because of we have a ":" in the counter name "Dist:Delivered Cmds/sec". The sp_verify_performance_condition extracted the names started from "Delivered Cmds/sec ....", that we had only 3 delimiter in the string(The first "|" was ignored), and got the error(as step 4 mentioned).

    To solve the issue, please add "SQLSERVER:" before the object name. The "SQLSERVER:" won't be recorded as the object name.
    Here is an example:
    N'SQLSERVER:Replication Dist.|Dist:Delivered Cmds/sec|fancyReplicationname|>|1000'

    If there is anything unclear, please feel free to ask.

    Thanks,
    Jinchun Chen

    • Marked as answer by BjörnBB Tuesday, July 22, 2014 2:28 PM
    Monday, July 21, 2014 6:42 AM

All replies

  • Hi hello,

    Thank you for your question. I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.

    If you have any feedback on our support, please click here.


    Elvis Long
    TechNet Community Support

    Monday, July 21, 2014 4:10 AM
  • Hi Björn,

    As you know, SQL Server extracts object name, counter name, instance name, operator and value from the @performance_condition, and then adds the alert. Before adding the alert, we need to verify the object name, counter name, instance name and the operator are correct by using sp_verify_performance_condition.
    The steps to verify the @performance_condition are:

    1. Verify the format is correct by using regular expression(as you commented)
    2. Get the position of the first ":"
    3. Start from the position got from step 2, extracts object name, counter name, instance name by delimiter "|". During the extraction, recording the number of delimiter
    4. Check whether the count of delimiter is 4. If not, raise error.
    5. Check whether the extracted names are existing in dbo.sysalerts_performance_counters_view

    In this case, the issue happened because of we have a ":" in the counter name "Dist:Delivered Cmds/sec". The sp_verify_performance_condition extracted the names started from "Delivered Cmds/sec ....", that we had only 3 delimiter in the string(The first "|" was ignored), and got the error(as step 4 mentioned).

    To solve the issue, please add "SQLSERVER:" before the object name. The "SQLSERVER:" won't be recorded as the object name.
    Here is an example:
    N'SQLSERVER:Replication Dist.|Dist:Delivered Cmds/sec|fancyReplicationname|>|1000'

    If there is anything unclear, please feel free to ask.

    Thanks,
    Jinchun Chen

    • Marked as answer by BjörnBB Tuesday, July 22, 2014 2:28 PM
    Monday, July 21, 2014 6:42 AM
  • Hi Jinchun,

    the "SQLSERVER:..." was the key to add my warning.

    Thank you!

    I try to add:

     N'Replication Dist.|Dist:Delivered Cmds/sec|replication|>|1000';
    

    But this one was the Key:

     N'SQLServerReplication Dist.|Dist:Delivered Cmds/sec|replication|>|1000';

    Funny...

    Best Regards

    Björn

    Tuesday, July 22, 2014 2:30 PM