am looking for some specific answer for automating monitoring of error log and event log. I saw many scripts out in the sites which are too complicated to understand. So currently I am in a dilemma which option should I select. Script or there is an option in SQL Server to add alerts.
So If we have added alerts then there is no need of scripts to monitor error log and event log ?
As long as you can write an alert for the condition(s) you need, alerts would be my preferred method. The feedback will be quicker and it will be easier to customize the alert (who gets it, how they get it, etc.) by severity or other criteria. Scanning error logs is better suited for analysis or trending, as you don't want to do it too frequently because of the performance hit.
You can also look at Event Notifications and Extended Events, these have a broader range of events that may be available through alerts. Make sure to do research though, as extended events do not have a native alert mechanism. They would be better suited for a job that polls results periodically similar to a log reader.
Thank you Robert. I could see three types in the alert option
- SQL Server event alert
- SQL Server Performance condition alert
- WMI event alert
Which option should I choose to set the alert for error log.
It depends on what type of event you want to be alerted about, not all events are available in each type.
If you're asking for a general alert on the error log (meaning anything is written to it), I'd recommend against that as there are a number of strictly informational messages that don't require any action (most will actually include that language in the message). Look at your existing logs and categorize which messages you want to be alerted about, then check the alert types to see which family it belongs to.
Here are few informative article which provides script to automate monitoring.
2 Likes