I had an inquiry about dbcc dumptrigger today and realized that state filtering was added to dbcc dumptrigger but not well known.
You can collect a process dump (SQL Dumper) when a specific error occurs using XEvent (error_reported/ex_raise2 events with dump capture action) or dbcc dumptrigger.
The common use of dbcc dumptrigger is: dbcc dumptrigger(‘set’, 208) to produce a dump when error 208 is encountered.
Syntax: dbcc tumptrigger(action, [opt] error number, [opt] state)
Actions
display: Output dumptrigger settings. (Note: Some errors are automatically enabled to capture dumps such as a 605 page corruption.)
dbcc traceon(3604)
dbcc dumptrigger('display')
set: Enable dump on error or error and state
dbcc dumptrigger(‘set’, error_number)
dbcc dumptrigger(‘set’, error_number, error_state)
clear: Disable dump on error or error and state
dbcc dumptrigger(‘clear, error_number)
dbcc dumptrigger(‘clear, error_number, error_state)
By default, the dump trigger remains ‘set’ until ‘clear’ is executed. However, you can limit each trigger to a single dump capture if you enable trace flag 8026 (dbcc traceon(8026,-1)). When the dumptrigger executes and the trace flag is enabled ‘clear’ is automatically execution, limiting the number of dump captures to one.
How Do I Determine Which Dump Triggers Are Enabled - Microsoft Tech Community
How It Works: Controlling SQL Server memory dumps - Microsoft Tech Community
