Setting up reporting and alerting in SQL Server through TSQL and Powershell

Question

I am trying to automate monitoring on SQL Server through PowerShell and TSQL, to get alerts through mail whenever some issue is there. I have gone through many articles over the internet and tried the approach but some of them are working and some are not. My question is if the database is having some issues like blocking, transaction log full, deadlock or errors in the error log, how to setup up reporting of these issues/errors through mail notification considering the DB Mail profile is setup.

Answer 1

Create agent event alert for the errors that are written to the event log. Here's a util I did that generates a bunch, and can serve as a starting point. https://karaszi.com/agent-alerts-management-pack

If sql server has a performance monitor counter for your condition, you can create an Agent perf on alert.

For those that aren't written to the event log, schedule a job that does whatever it is you want to check for and send you the email(s).

Above is if you want to roll your own, of course. A alternative is to shop around, see what products exists in this space.