Alerts not working

Resolution/Summary:

1. Opened the MOSS Central Administration page, clicked on Operations--Timer Job Status, made sure the following two jobs were showing "success" and 100%
·         Change Log 
·         Immediate Alerts

2.    Opened the command window, navigated to the BIN folder and got the property of Alerts-enabled to make sure the alerts are enabled for the timer service:
“C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsadm.exe -o getproperty -url <http://problemsite> -pn alerts-enabled”
The output was
<Property Exist="Yes" Value="yes" />

3.    Made sure the account running the SharePoint Timer service is the account which has the administrator Full Control permission over the site and the full permission over the content database. Then we restarted the timer service.

4.    We found EventData and ACL are not NULL for the specific content database. Found This seems to be the key to the problem.

5.    We checked the property job-immediate-alerts schedule
“C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN>stsadm.exe -o getproperty -url <<http://ProblemSite>> -pn job-immediate-alerts”
The output was
Property Exist="Yes" "every 5 minutes between 0 and 59"

6.    Ran the following query against the content database of problematic site "select * from eventcache where EventData is not null" and found the 74 entries.

7.    Ran the following query “Select * from timer lock” and found the Timer service server and restarted the timer service on the server.

8.    Ran the Query "select * from eventcache where EventData is not null" and found that the it didn’t become NULL.

9.    Restarted the timer service in all the servers and deleted the cache files in “C:\Documents and Settings\All Users\Application Data\Microsoft\SharePoint\Config”.

10.After five minutes we got 50 alerts Emails.

11.Ran the "select * from eventcache where EventData is not null" and didnt find any entries.

12.Ran the "Select * from timerlock " and found that the timer server was changed.