Over the weekend one of our jobs failed with error “insufficient memory available in the buffer pool“.
This led to thinking that we should have automatic alerts for resource issues.
So create a new Alert by doing right click on SQL Server Agent => New => Alert…
Select severity => 017 Insufficient Resources.
navigate to Options tab and check on E-mail to get the error text in email.
Thus we setup automatic alerts on SQL server for insufficient resources.
Reference: Setting up alerts on SQL server
SELECT severity,message_id,is_event_logged, [Text]
WHERE language_id = 1033 and severity=17
ORDER BY severity desc, message_id asc