Working with Alerts
In a situation where we have thousands of alerts waiting in event cache table for processing but due to some issue they are keep accommodating there.
May be the property for job-immediate-alerts is set to “no”
try running query
select * from eventcache where EventData is not null
and you will find thousand items there for alert
Now issue is if set the properly to yes it will flood the mail box of user and also put load on sql and exchange server
Solution :-
Go to central admin and removed the value in out going email settings.
Now get the property of immediate alert to Yes by
stsadm -o setproperty -pn job-immediate-alerts -pv "every 1 minutes between 0 and 59" -url https://SiteURL
or follow other available solution which you want to try but you worry about the thousands of pending alerts
keep eye on the evnetcahe table now the items should move from event cache and event log .
Once all the unprocessed items for alerts is moved to event log . now put the value back in central admin for outgoing email settings.
Few useful commands
stsadm -o getproperty -pn alerts-enabled -url https://SiteURL
stsadm -o getproperty -pn job-immediate-alerts -url https://SiteURL
stsadm -o getproperty -pn job-daily-alerts -url https://SiteURL
stsadm -o getproperty -pn job-weekly-alerts -url https://SiteURL
stsadm -o setproperty -pn job-immediate-alerts -pv "every 1 minutes between 0 and 59" -url https://SiteURL
stsadm -o setproperty -pn job-daily-alerts -pv "daily between 22:00:00 and 24:00:00" -url https://SiteURL
stsadm -o setproperty -pn job-weekly-alerts -pv "weekly between Fri 22:00:00 and Sat 24:00:00" -url https://SiteURL
How alert works
1. The EventCache table records the SQL level events as they occur and the
EventData and ACL columns are not NULL for an alert event.
2. There is an alerts timer job that runs periodically to process the records in
the EventCache. After the alerts timer job runs, it nulls out the EventData and
ACL columns.
3. Then, it will log an event into the EventLog table.