Troubleshooting problems with SQL Server 2014 alerts
If you have problems with SQL Server 2014 alerts, review this troubleshooting checklist
to find potential solutions.
1. Install the latest SQL Server 2014 service pack.
Because some SQL Server 2014 alerts bugs were fixed in SQL Server service packs,
you should install the latest SQL Server service pack.
At the time this article was written the latest SQL Server 2014 service pack was
service pack 1. You can download the SQL Server 2014 service pack 1 at here:
https://www.microsoft.com/en-us/download/details.aspx?id=46694
2. Ensure that the SQL Server Agent service is running.
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.
For example, to start SQL Server Agent service, choose:
Start -> Settings -> Control panel -> Administrative Tools -> Services
Select SQL Server Agent service then choose startup “Automatic” type and click Start.
3. Check 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 or pagers,
you must set the account the SQL Server Agent service runs under to be a member of
the Domain Users group.
4. 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.
5. Check the permissions of the user that 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.
6. 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 new ‘Delay between responses’ setting.
7. Check the alert’s history to determine the last date the alert worked fine.
To view the history values of 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 you want to check history, and then click Properties.
– Select the History page to view the date the alert last occurred, the date the
alert was last responded to, and the number of times the alert was triggered since
the last time the count was reset.
8. Avoid using “net send” and “pager” notifications.
Use “e-mail” notification instead of “net send” or “pager” notifications because
the “pager” and “net send” options will be removed from SQL Server Agent in a future
SQL Server version. Microsoft recommends avoiding using these features in new
development work, and plan to modify applications that currently use these features.
9. The error 14527 may occur when you try to use job in alert.
This is the error message text: “Job “%s” cannot be used by an alert”. To work around
this problem, you should first associate this job with a server by calling sp_add_jobserver.
10. You can get the error 32050 when you try to create alert on the system database.
This error indicates that alerts cannot be created on the master, msdb, model or tempdb
database.
11. The error 45210 may occur when you try to use Database Mail for alert notifications.
This is the error message text: “Database Mail is not enabled for SQL Agent to use for
Notifications”. To work around this problem, you should enable Database Mail as the mail
system for alerts.
12. Check SQL Server error log, SQL Server Agent error log, and Windows Application
log to get more detailed error description.
Comparing the dates and times for alert failure events between the SQL Server error
log, the SQL Server Agent error log and the Windows Application log can help you to
determine the reason of the failure.
13. Data Alerts tables are filled up and never clear old data when you use alerts
for SQL Server 2014 Reporting Services (SSRS 2014) reports.
This problem occurs when you have configured SSRS 2014 to run in SharePoint Integrated
mode. This bug was first fixed in Cumulative Update package 7 for SQL Server 2014.
You can download the Cumulative Update package 7 for SQL Server 2014 at here:
https://support.microsoft.com/en-us/kb/3046038