SQL Server

Some tips to work with SQL Server 2016 alerts

Some tips to work with SQL Server 2016 alerts

SQL Server 2016 writes the events into the Windows Application log. SQL Server Agent

checks the Windows application log for SQL Server events. When an event occurs, the

SQL Server Agent checks if the appropriate alert exists and if so perform the defined

response. Alerts can be used to execute a SQL Server Agent job or send notification

message via e-mail.


Specify the understandable alert name.

Try to specify the alert name so, that the name describes what the alert makes.

Alert names must be unique within the instance of SQL Server 2016 and can be
no longer than 128 characters.


Ensure that the SQL Server Agent service is stared and configured to start

automatically.

This service must be started, if you need the alert be fired. So, if the

SQL Server Agent service is not running, you should start it. To start

SQL Server Agent service you can use Windows Control panel.

Check the permissions of the user, who need to create, modify, delete,

enable/disable alerts or view/edit alert properties.

By default, only members of the sysadmin fixed server role can create, modify,

delete or enable/disable alerts. The members of the SQLAgentOperatorRole database

role can enumerate (view list) alerts and view alert properties.

Increase the size of the Windows application log.

It is necessary to avoid losing SQL Server 2016 event information.

You can set an alert to response to a particular Windows Management

Instrumentation (WMI) event.

To select a WMI event, you must define the Namespace and Query parameters on the

SQL Server Agent General page of the New Alert or the Alert Properties dialog box.

Specify the database name.

By default, when you create alert, this alert can be responded on the event

for all databases. When you specify the particular database name, you reduce

the area the alert can respond. Using the particular database name increases

the alert performance.

Consider creating SQL Server job to respond to event instead of sending

notification message via e-mail.

In general case, it is better to create a job that will correct problem when the

problem exists than send a notification message to the operator. In the first case,

the problem can be resolved by the job automatically, in the second case, you will

have some delay because the operator can be temporary unavailable and so on.

If the alert fires, but the notification is not timely, decrease the “Delay between

responses” setting for the alert and try to send notifications to as few operators

as possible.

To decrease the “Delay between responses” setting for the alert, you can do the

following:

– In Object Explorer, connect to an instance of the SQL Server Database Engine.

– Expand that instance, expand SQL Server Agent and expand Alerts.

– Right-click the alert, and then click Properties.

– Select the Response page and specify the “Delay between responses” setting.


Ensure that the alert is enabled, if the alert is not firing.

The alerts can be enabled or disabled. To check that alert is enabled, you can do

the following:

– In Object Explorer, connect to an instance of the SQL Server Database Engine.

– Expand that instance, expand SQL Server Agent and expand Alerts.

– Right-click the alert you want to enable, and then click Properties.

– Turn on the Enabled option.

Consider using Reporting Services data alerts.

SQL Server 2016 supports Reporting Services data alerts. Reporting Services

data alerts are a data-driven alerting solution that informs you about changes

in report data at a relevant time. Data alerts messages are sent by email.

Note. The Reporting Services data alerts feature is available only when you

install Reporting Services in SharePoint mode.


Ensure that account the SQL Server Agent service runs under is a member
of
the Domain Users group.

The LocalSystem account does not have network access rights, so if your jobs

require resources across the network, or you want to notify operators through

e-mail, you must set the account the SQL Server Agent service runs under to be

a member of the Domain Users group.

If the alert fires, but the responsible operator does not receive notification,

try to send e-mail message to this operator manually.

In most cases, this problem occurs because you have entered incorrect e-mail

addresses. If you can send e-mail message manually to this operator, check the

account the SQL Server Agent runs under and check the operator’s on-duty
schedule.

Facebooktwittergoogle_plusredditpinterestlinkedinmail