SharePoint Alerts & Task Notifications not working -- things I tried

In case you guys are searching for the things you tried in trying to fix this, below is what i tried before I found the answer, documented HERE.

good luck!


Here is the setup:
The client has nonupgraded MOSS 2007 on a small server farm setup with on web application containing several site collections. Each site collection has a separate content DB. Recently, alerts and task assignments stopped working for some reason on their prod and dev servers -- this is why the client brought in my company to see if we can fix things. Alerts and task notifications are vital to the way they do business.

First thing we did was install the rest of SP1. They only had WSS SP1 installed previously. That did not fix the problem. I then ran stsadm -o updatealerttemplates & iisreset on dev. this seemed to clear up the alert problems in dev, but the same actions in production did not change anything.

In order to trouble shoot, I created a new web application on the same server, added a site collection, and added an alert to a task list and added some tasks. Bam! we get an alert notification that something changed on the list and also a notification that a task was assigned to me. So now I have 2 web apps with the same security setup on the same server, one with working alerts and one without.


Here is a list of the other things I've tested/tried:
- verified that Immediate Alerts timer job and SharePoint Timer Job are running successfully.
- verified my test user has full access to the task list and the containing site and the top level site of the web app.
- on new web app, same site , added 2nd task list -- alerts work (had read that each web app can only have 1 task list with "notify on assignment" checked)
- on old web app, same site, added new task list -- alerts do not work -- followed alert through database and saw that it was written to EventCache table with non-null EventData column that gets cleared out when Immediate Alerts timer job runs
- on old web app, different site collection, added new task to existing list -- alerts do not work
- on old web app, create new site collection with task list -- alert works, task notification does not. alert notification didnt' come in til about 1 hr after i created the task
- checked that site properties were set up correctly using stsadm -o getproperty -pn alerts-enabled and -pn job-immediate-alerts. set to "yes" and "Every 5 minutes between 0 and 59"
- reset these properties (turned off and then back on)
- verified that timer job account has dbo access to content dbs for sites on old web app and on new web app
- noticed that enterprise feature was not activated on old web app but was activated on new web app. deactivated enterprise features on new web app and got alerts still, but no task assignment notification. tried activating enterprise features on old web app, but no change in alert behavior.
- took a site collection from old web app, detached it's content db from old web app. reattached this content db on new web app. recreated alert. added new task --> get alert notification, but no task assignment notification. At this point, on the new web app, i have 1 site collection with working alerts/tasks and 1 site collection with only alerts working.

I also checked in the SP logs and noticed that when alerts/tasks worked, I would get messages like this:
AlertsJob loaded 1 of 1 event data records AlertsJob loaded 2 of 2 subscription records Alertsjob results for immediate delivery: 2 prematches, 2 passed filtering, 2 of 2 passed security trimming, 2 final after rollup Alertsjob results for scheduled delivery: 0 prematches, 0 passed filtering, 0 of 0 passed security trimming, 0 final after rollup AlertsJob processed 2 immediate notifications in 2 digests, sent 2 emails, failed to send 0 emails

when they did NOT work, I'd get messages like this:
AlertsJob loaded 0 of 0 event data recordsAlertsJob loaded 0 of 0 subscription records Alertsjob results for immediate delivery: 0 prematches, 0 passed filtering, 0 of 0 passed security trimming, 0 final after rollup Alertsjob results for scheduled delivery: 0 prematches, 0 passed filtering, 0 of 0 passed security trimming, 0 final after rollup AlertsJob processed 0 daily notifications in 0 digests, sent 0 emails, failed to send 0 emails