Email Alert Log
Overview
Email alerting sends you a message when something crosses a threshold. The Email Alert Log is where you find out what it actually sent, and which alerts are still firing.
The page has two tabs, and they answer two different questions.
- Alert History. Every alert that has ever fired, newest first, over a time frame you choose. What have we been getting told about?
- Current Alerts. Every alert in an active state right now. What is unresolved?
Between them they answer the two complaints people have about alerting: I am getting too many of these and is that one still going?
Where to find it
An instance level report. Right-click the server → Instance Level Reports → Email Alert Log.
The page title reads Email Alert Log for <server name>.
Requirements
- SQL Server 2008 or newer. Older instances get a message saying so.
- A
DBHealthHistorydatabase on this instance, holding the alerting tables. Email alerting writes its history there, so an instance with alerting not set up has nothing to show.
Alert History
The default tab. One row per alert that fired.
| Column | What it is |
|---|---|
| Alert Hit Time | When the alert fired. |
| Alert ID | Which configured alert it was. |
| Tag | What it fired about: the database, the drive, the job, whatever the alert is scoped to. |
| Warning Message | The message that was sent, with the HTML stripped out so it reads as text. |
Sorted newest first.
Time Frame limits how far back the tab looks: 1 Day, 1 Week, 1 Month, 6 Months or All Time. It opens on 1 Week.
The Tag column is what makes the history usable. One alert firing about six different databases is six rows with the same Alert ID and six different tags, which is what lets you see that the alert is not the problem, one particular database is.
Current Alerts
Everything currently in an alerting state.
| Column | What it is |
|---|---|
| Last Hit Time | The most recent time this alert fired for this tag. |
| Alert ID | Which configured alert. |
| Tag | What it is about. |
| Alert Status | ALERTING on a red background, or OK on green. |
| Last Warning Message | The most recent message for this alert and tag. |
| Next Alert Time | When it would send again if the condition is still true. |
The Time Frame dropdown is hidden on this tab, because there is no time window to choose: current is current.
Next Alert Time is the anti-spam interval doing its job. An alert that is still true does not send every minute; it sends again when this time passes. A Next Alert Time in the future on a condition you have just fixed means you will not get another message about it, and the row stays until the alerting run notices the condition has cleared.
Reading one entry
Double-click any row to open the full detail in a window: the alert, the tag, the times and the whole warning message laid out rather than squeezed into a grid column. Messages are usually longer than the column, and this is where you read them.
Right-click actions
Which items appear depends on which tab you are on.
| Item | Tab | What it does |
|---|---|---|
| Clear Alert | Current Alerts, on an ALERTING row | Marks that one alert and tag as no longer active. |
| Clear All Alerts | Current Alerts | Marks every active alert as no longer active. |
| Delete Selected Entry | Both | Permanently deletes the selected rows. |
| Delete All Entries | Both | Permanently deletes everything the tab is currently showing. |
Clearing and deleting are not the same thing, and the difference matters.
- Clearing resets an alert’s state so it is no longer considered active. The history of it firing stays. Use it for an alert that is stuck on after the underlying problem has been fixed.
- Deleting removes the record. On the History tab it removes the record that the alert ever fired.
Clearing does not fix anything. If the condition is still true, the next alerting run will set the alert active again and send another message. Clear an alert when you have dealt with the cause, not instead of dealing with it.
Delete All Entries respects the time frame on the History tab. With the time frame on 1 Week it deletes the last week, not everything, and the confirmation says so. On the Current Alerts tab it deletes all of them. Both ask first, and neither can be undone.
Configure Alerts
The Configure Alerts button opens the email alerting configuration for this instance: which alerts exist, their thresholds, who they go to and how often they may repeat. Closing it refreshes the page, so a change to an alert is reflected straight away.
That dialog is where alert fatigue gets fixed. This page is the evidence for the conversation; the configuration is where you act on it.
How to read the report
- Start on Current Alerts. Anything red is unresolved right now.
- Then read History over a week. Volume is the thing to look at, not individual rows.
- Group by Alert ID in your head. One alert producing most of the rows is either the most valuable alert you have or the one whose threshold is wrong.
- Then group by Tag. One database or one drive producing most of the rows is a real problem rather than an alerting problem.
- Clear what you have fixed. A stale ALERTING row costs you the ability to trust the tab.
Common patterns
One alert, one tag, hundreds of rows. A condition that has been true for a long time and an interval that is too short. Fix the condition or lengthen the interval; do not just delete the rows.
An ALERTING row with a Last Hit Time from weeks ago. The alert fired, the condition went away, and nothing cleared the state. Clear it.
Alerts clustered at the same time every night. Maintenance triggering a threshold. Job Schedules shows what runs then, and the alert threshold probably needs to allow for it.
History full, Current Alerts empty. Alerting is working exactly as intended: things fired, things resolved.
Nothing in either tab. Either nothing has crossed a threshold, or alerting is not configured for this instance. Configure Alerts tells you which.
Where the data comes from
[DBHealthHistory].[dbo].[MailReportingHitLog]for the history and the messages.[DBHealthHistory].[dbo].[MailReportingCheckTracker]for which alerts are currently active.
Current Alerts joins the tracker to the most recent hit log entry per alert and tag, which is where the message and the next alert time on that tab come from. An active alert with no matching hit log entry still appears, with those columns blank.
Warning messages are stored as HTML because that is what is emailed. The page strips the markup and collapses the whitespace so the grid shows the sentence rather than the tags.
This page writes to the history database when you clear or delete. Everything else is a read.
Related reports
| Report | Why you would go there |
|---|---|
| Failed Jobs | The job failures behind a job alert. |
| Backup Status | The exposure behind a backup alert. |
| Disk Space and Disk Space Forecast | The volume behind a disk space alert. |
| Error Log | What SQL Server logged around the time an alert fired. |
| Job Schedules | What runs at the time alerts cluster. |
| Performance History | Whether the metric behind a threshold alert has been drifting for weeks. |
Frequently asked questions
Both tabs are empty. Either nothing has fired, or email alerting has not been set up for this instance. Open Configure Alerts to check.
What is the difference between clearing an alert and deleting it? Clearing resets the state so the alert is no longer considered active, and keeps the history. Deleting removes the record.
I cleared an alert and it came straight back. The condition is still true. Clearing resets the state; it does not fix the cause.
Why can I not clear a row that says OK? Because it is not alerting. Clear is only offered on an ALERTING row.
Does Delete All Entries delete everything? On the History tab it deletes what the current time frame covers, which the confirmation states. On the Current Alerts tab it deletes all of them.
Why does the Time Frame dropdown disappear? Because you are on Current Alerts, which has no time window.
Does this page send email? No. It shows what was sent and what is active. Sending is done by the alerting run.
Why do several rows share one Alert ID? Because that alert fired for several tags, for example one database each. The Alert ID is the rule; the Tag is what it fired about.