SQL Alert for stack dumps

  • I want to create a custom SQL Agent Alert for whenever a stack dump occurs.  However I can't seem to determine what the stack dump error number is (for example Error 1205 Deadlock Detected) or what severity level (001 - 025) it would be.  Does anyone know?

  • There isn't a specific error number for stack dumps. Have you considered doing something like monitoring sys.dm_server_memory_dumps? There is a row in the view every stack dump that is generated by SQL Server. You can use a job to poll the view and send an email if it finds new rows.

     

    Sue

     

  • Yeah, we tried an INSERT trigger on that DMV but apparently you can't put a trigger on a DMV.  🙁

  • No you can't put a trigger on it but I don't think would need an insert trigger. That's why you would poll the view. If nothing else and you aren't going to delete anything in the view, you could track the last time the view is checked in some admin table and on each check just use greater than last check time for the creation_time in the view. If you do that in a job and records exist then send an email.

     

    Sue

  • If I have more than 50 servers do i need to create the Job in all 50 servers?

  • Is it good to run every 15 mins?

  • Admingod wrote:

    If I have more than 50 servers do i need to create the Job in all 50 servers?

    No.  Look for Get-DBADump at the following link.  It's part of the infamous DBATools collection of PowerShell driven tools.

    https://dbatools.io/commands/

    The following appears to be a post on how to use it against multiple servers.

    https://www.powershellgallery.com/packages/dbatools/0.9.832/Content/functions%5CGet-DbaDump.ps1

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply