SQL Alert for stack dumps

Question

SQL Alert for stack dumps

Mick Opalak

SSCommitted

Points: 1631
More actions
May 9, 2019 at 7:04 pm

#3639300

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?

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

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

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

Mick Opalak SSCommitted Points: 1631 More actions · Answer 2

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

Sue_H SSC Guru Points: 90860 More actions · Answer 3

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

Admingod SSCertifiable Points: 5997 More actions · Answer 4

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

Admingod SSCertifiable Points: 5997 More actions · Answer 5

Admingod

SSCertifiable

Points: 5997

September 17, 2020 at 11:32 pm

#3789285

Is it good to run every 15 mins?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

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)