Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    95

    Unanswered: Alerts Not Working

    I defined an Alert in Enterprise Manager for Severity 19 - Fatal Error In Resource. The alert is enabled, the type is "sql server event alert" and the alert is defined against a specific database. SQLSERVERAGENT and SQL Mail session are both started. I test the alert using the following statement in the specified database.

    raiserror (50001,19,1) with log

    A message is placed in the SQL Server log, however no alert is fired. The alert history indicates the alert has never occurred. What am I missing?

    Thanks, Dave

  2. #2
    Join Date
    Jan 2003
    Posts
    95
    Here's an update.

    I refreshed EM earlier in the day and still saw no history. This time I disconnected and reconnected the instance. For some reason I have to do this in certain situations. After reconnecting I noticed a history existed under a demo for Severity 19. I didn't realize the demos were enabled. I just disabled the demo alert and all appears to be working.

    Thanks, Dave

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    all the demos are enabled for each of the severity levels if you are defining errors soley based on severity they will as you found out hit first
    also ...
    [BOL] Says
    Error messages with a severity level of 19 or higher stop the current batch. Errors messages with a severity level of 20 or higher are considered fatal errors and terminate the client connection. Errors messages in this range may affect all of the processes in the database, and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 25 are written to the error log.
    you might want to consider refining your alert to fire on the event id's that are acutally being thrown..
    just a suggest

    by the way good debugging on your part.
    Last edited by Ruprect; 03-23-04 at 04:06.

  4. #4
    Join Date
    Jan 2003
    Posts
    95
    The error message I'm seeing is being generated by a vendor's software package.

    Error: 1204, Severity: 19, State: 1
    The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration..

    There are multiple performance problems associated with this application. I suspect the error above is connected to many of the problems. I don't believe the database is damaged. I'm guessing it's just a matter of poorly written code. Now I have to figure out how to setup Profiler to look for this error.

    Thanks, Dave

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    http://www.sql-server-performance.com/deadlocks.asp to prevent deadlocks and error defined.

    Ensure to run the statement in batches if it is affecting large number of rows. Another option is to specify a hint like table or page lock so that the number of locks can be reduced.

    What are lock settings defined at SQL server?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Jan 2003
    Posts
    95
    The lock setting is still at the default of 0. I'm not sure what code is causing this problem to occur. I need to setup Profiler to trap the logic. I just setup Profiler to look for Severity 19, but the filter does not appear to be working. I'm still seeing all server activity. Am I missing something?

    - Filters
    - Severity
    - Equals 19

    Dave

  7. #7
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Oh, then follow this link http://www.sql-server-performance.com/blocking.asp to monitor the blocking and capture the trace.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Not missing much about profiler. Any null value is returned as a valid hit. You may want to just run profiler to a table with the SQL statement complete, RPC complete, and some of the error events getting logged. Once you have a pile of data in a table, you can run queries off of that for severity 19, then try to work back via the identity column to figure out what statement did it.

  9. #9
    Join Date
    Jan 2003
    Posts
    95
    Can you tell Profiler to ignore NULL?

    I'll give your suggestion a try.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •