This is the first post about “troubleshooting MOSS/WSS” on a series, defined by topics and Issues.

Other related posts following this series has been published here:
"Alerts in SharePoint Part II (Troubleshooting MOSS/WSS)"  
"Alerts in SharePoint 2010 (Troubleshooting)" 

(applies to: Microsoft Office SharePoint Server 2007;  Windows SharePoint Services 3.0)

Content:

• Alerts in SharePoint 2007
• The typical issue
• Examples of common issues
• Alerts are not working ...
• General troubleshooting steps
• Additional Links and related resources
• Some technical details
  • Security Trimming
  • Logging
  • Backup and Restore
• Immediate Alerts
• Scheduled Alerts
• Search based Alerts (Posted by Victor Butuza, a colleague ;-)
• Database tables
• How to troubleshoot alerts?
• Troubleshooting steps
  • SQL Queries

Alerts in SharePoint 2007

I think one of the most reported and popular issues within SharePoint Server 2007 / WSS (and have been also before on SPS 2003) is:  the “Alert problem”.

First understand whether the emails are even sent out by the SharePoint server. If alerts are sent out by SharePoint and you have seen them through the SMTP traffic then you need to concentrate on the factors external to SharePoint like problems with network or the email server related issues or for that matter, problems with specific email addresses in receiving emails. However if you see that the SharePoint server is not even sending out the emails, you should first ensure that you have followed and configured according to the published TechNet articles here:

Configure outgoing e-mail settings (Office SharePoint Server)
Configure outgoing e-mail settings (Windows SharePoint Services)
Plan outgoing e-mail (Office SharePoint Server)
Plan outgoing e-mail (Windows SharePoint Services)
Configure outgoing e-mail settings for a specific Web application (Office SharePoint Server)
Configure outgoing e-mail settings for a specific Web application (Windows SharePoint Services)

Well, when we’re talking about “alerts” it usually means the “Notification email” that is sent to a user. For exapmle, this is mostly set on a document library where users configuring their “Alerts” for getting notified on certain actions like changes on documents, new added, deleted, modified etc.

The process of creating an alert for the end user is quite simple. The alerting system is exposed in the SharePoint site via links labeled "Alert Me" on the Action menu. The user then selects the circumstances under which and the interval for the alerts to be delivered. There are quite a few options that allow for a variety of alerting scenarios. There are some options when it comes to the circumstances that generate an alert.

There are two elements to these circumstances.  The first is the type of change that will generate the alert:

* All changes  * New items are added  * Existing items are modified 

In WSSv3 we have the ability to create additionally a filter to be even more specific so that items the user changes do not generate alerts unless you want it as defined by the filter.
You can also limit the alerts to items created by or modified by the user. 

Additionally you can filter an alert that would appear in a particular view of the list (i.e. Only alert me when one of My Tasks changes /  only alert me when someone adds items that appear in the view [View Name]).

The final option in creating an alert allows the user to choose the frequency (assuming some change) that alerts will be sent.  
The options are to create an immediate alert which are sent at most every 5 minutes (when the timer service runs) or a digest alert which can either be sent daily at a specific time or weekly on a specific day and time.

Unfortunately sometimes the alerts are stop working or don't work at all for a specific configuration or just don't work as the end user expects it.
That's when we start troubleshooting and investigation on alerts and may end by head banging on the wall or some like this ;-)

Back to Top

So lets first get through some most common scenarios an alert does not work as we expect! 

The typical issue:

You create an alert on a document library. You get the notification that the alert has been created. But you don’t get any alerts sub sequentially.

So what I‘ll try to tell you today is a little “toolbox“ you can use on troubleshooting your environment while investigating problems with your alerts.

First of all, we should split of some wording when we’re talking about “Alerts”:
- the initial email, sent to you with notification that you have set and/or created an alert
- the following emails (subsequently) sent to you, when you have configured to be notified on any changes to a document, Item, List entry a.s.o.
- the search based alerts …
- the workflow initiated email alerts for i.e. assigning you a task.

As you see, we may handle several different kinds of “Alerts” and also why or if they sent or not.
Therefore it is often quiet difficult to troubleshoot the cause of those issues as they may vary each time.

From my daily business I often have very tricky cases and issues. But sometimes and despite the Internet,
some “simple resolutions” also may work based on native settings or better let’s say: 
”why can’t we see the forest about all those trees in front of it?”  - sounds simple, doesn’t it?  But I can hear also some “uuuhh yeahhh oooahhh” but what’s the joke?” 

Examples of common issues:

Let’s check some examples where the cause and resolution were quiet simple but not noticed on the first research:
-------------------------------------------------------------------------
Issue 1:  “Some email alerts are sent but some others are not…” 

Problem:

We had users registered for alerts. The “initial email” was sent to the user, notifying him that he just created an alert. Now another user changed the document/item you marked for an alert but you don’t  get it!

Resolution:

The resolution was as simple as seeing the “forest behind the tress” ;-) When you have typically a load balanced environment and also using an exchange server for all email traffic,
you should have a look also into the “mail relay settings of the SMTP Server”

Cause:

The IP addresses that were configured to allow relay did not include the IP address for one of the front end servers. Adding that IP address resolved the problem.

-------------------------------------------------------------------------
Issue 2:  “All users are getting alerts but only one user not”

Problem:

- In this case, just to keep it short, it was Outlook causing it and moved all email alerts into the “junk mail folder” of the affected user
Simple, isn’t it?

Ok, but this is “fortunately” not the usual case and should be only to show up how troubleshooting sometimes can be so easy
when you’re able to “see the forest BEHIND the trees”.

Back to Top 

Alerts are not working…

Coming back to some more difficult and more interesting causes of almost similar problems:

1. Migration Issue:

One common issue regarding suddenly not working email alerts can occur after migrations and/or detach/re-attach databases to a different web application.

The cause here is  that the issue occurs if the URL of the Windows SharePoint Services 2.0 server differs from the URL of the Windows SharePoint 3.0 server. For example, this issue occurs if the URL of the Windows SharePoint Services 3.0 server is http://ServerNameVersion3, and the URL of the Windows SharePoint Services 2.0 server is http://ServerNameVersion2.

For this you may find a little sample code chunk here, which should you help to fix such an issue:
E-mail notifications for alerts are not sent when content in a migrated list or in a migrated document library changes after you perform a database migration to upgrade to Windows SharePoint Services 3.0 (KB 936759)

Resolution: 

Additionally you can also try to fix it by using the new "stsadm -o updatealert" command and the SharePoint Administration Toolkit v4  (x64 version) ; (x86 version).

Back to Top  

2. Setproperty- Issue:

Another cause also mostly occurring after migrations is the missing or not correct set property value for the alerts at all or in particular on your site-url. To check those properties you just open a command box, navigating to the 12-hive BIN folder (by default:  C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN…) and run STSADM command line tool

Once on 12-hive, type in the following commands to check all related property settings:

stsadm –o getproperty –url http://YourURL –pn alerts-enabled         
-the expected result should be  <Property Exists=”Yes” />

stsadm –o getproperty -url http://YourURL –pn job-immediate-alerts      
-the expected result should be  <Property Exists=”Yes” Value=”every 5 minutes between 0 and 59” />  
where the Value type may vary.

When  the properties are not set correct or even set but due to migrations, database restore or detach/re-attaching it, the alerts may stop working for no obvious reason.

Resolution:

Run the stsadm commands to “set” the properties correct or just to trigger SharePoint once more to processing it.

stsadm –o setproperty –url http://YourURL –pn alerts-enabled –pv True

stsadm –o setproperty –url http://YourURL –pn job-immediate-alerts –pv “Every 5 minutes between 0 and 59" 

You can specify for the property “job-immediate-alerts” one of the following values:
•"Every 5 minutes between 0 and 59"
•"Hourly between 0 and 59"
•"Daily at 15:00:00"
•"Weekly between Fri 22:00:00 and Sun 06:00:00"

Please see here for options and syntax of the commands:

Alerts-enabled: Stsadm property (Office SharePoint Server)
Job-immediate-alerts: Stsadm property (Office SharePoint Server)
Index for Stsadm operations and properties

Back to Top

3. Scheduled Alert Issue:

Another finally very simple cause is that SharePoint for some reasons sometimes needs to be “reminded” on what’s its job on alerts ;-) Latest I had the case with problems on not sent “scheduled alerts”. The initial alerts as well as the immediate alerts were sent properly but no scheduled alert (which is a summary of certain changes, notified daily, weekly etc.) received the user.

After extensive troubleshooting and research we finally just ran again a stsadm command to re-register the alert template on the server and “oh wonder”, suddenly the alerts worked again. 

Resolution:

Run stsadm -o updatealerttemplates -url http://YourURL -f C:\Alerttemplates.xml –lcid 1033

Please see here for options and syntax of the commands:   Updatealerttemplates: Stsadm operation (Office SharePoint Server)

Back to Top  

General Troubleshooting Steps:

First of all, you should verify the most obvious but also mostly not considered options on very simple tests:

- Check your Outgoing Email settings on SharePoint / SMTP Settings on WFE/IIS and/or Mail relay permissions and restrictions.
-> Is email sending in general working? DO you get any email at all from SharePoint?

- Check if your email notifications and/or alerts are fired only partly (i.e.  immediate but not scheduled)
-> If the initial email at all is delivered but no subsequently alert emails, first exclude any Outlook client problems like junkfilter, blocklists, etc.

- Check in SharePoint on Central Admin page, under “Operations” the Timer job Definitions and status.
- Check if the timer jobs finished successfully at all and verify the last date the job was running!

One stage deeper:

- Check the alert properties via stsadm, if they’re set properly (see above "setproperty issue")

- Re-register the alerttemplate again. Sometimes this also may fix already your issue ;-)

stsadm -o updatealerttemplates -url http://YourURL -filename "c:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\template\xml\alerttemplates.xml" -LCID 1033

Note! LCID is the code for your local language you're using!

- Check if the issue only occurs on a particular site collection, web application or only a List

(to test this, just create a brand new Web app, Site collection and leave all on default. Create alerts as applicable and check the delivery)

• If the alerts are fired on a new web app/Site collection, then consider the effort to just export your content from the faulty site and import it to the working site.
  
• If the alerts are not fired at all, use Network Monitor, Process Monitor traces, to see if any email at all arrives your mail relay/Exchange server or not. IF required, configure on your mail server the necessary relay permissions for the SharePoint Servers.
  
• If the alerts are not working only on certain libraries or sub sites or even just randomly, check for custom event handler or other parts that you may implemented or changed to performing email alerts. Verify all steps before and try to check whether alerts are working on default settings but not on your custom settings.

Back to Top 

Additional Links and related resources:

Alerts in SharePoint Part II (Troubleshooting MOSS/WSS)
Alerts in Windows SharePoint Services
SharePoint Alert Manager

Another very good blog from my colleague Victor Butuza describes the topic "Search based Alerts"
with some further hints and tips on troubleshooting alerts to be fired on search results!

Back to Top   

Some Technical details on Alerts:

Once you have done all the previous investigations and tests but still having no glue or idea what may causing your alerts issue,
you can dig a bit deeper into the SharePoint Mysteries:

What's Under the Hood?
Alerts are processed by the OWSTimer job. In central admin you may see just the “Immediate alerts” job, but this one processes both, the immediate and the scheduled alerts at intervals of every 5 minutes by default. When you create an alert a stored procedure in SQL Server will run to make the database changes to add the alert. Essentially, this inserts various values into the ImmedSubscriptions or SchedSubscriptions tables in the content database. On the SchedSubscriptions table we add some more columns to handle the timing of alert delivery. In these tables are stored some key values we need to flag each alert type for the further processing.

Alerts will be security trimmed at send time, ensuring that unauthorized access to content is not possible.

Security Trimming
Unlike in WSS V2, security trimming will be a key component of the alerts infrastructure SharePoint will check both when the user creates an alert and when sending an alert that the user has permission to view the item.

There are two steps to the process of alerts security trimming:

1.      When list items are added, modified, or deleted:
         a.      SharePoint records item metadata in the alerts event cache for the changed item.
         b.      SharePoint does a lookup on the Permission table for the item and stores it in the alerts event cache as dbo.eventcache.acl.

2.      When alert events match an immediate/daily/weekly subscription:
         a.      Processing is segmented by site collection, so that cached user tokens are used most effectively.
         b.      The users WSS token is retrieved from the User table.
         c.      A request is made to the AD for each receiving users NT token.

3.      An ACL check is done with the stored binary ACL blob, users WSS token and NT token for read access.

Back to Top  

Logging
Alert processing and notification sending will be logged. The following table describes the events logged, in both verbose (logs all events, for reference) and exception modes (exceptional events the admin needs to be aware of). 

Back to Top  

Backup and Restore
Alerts will get backed up with site content. Restore will restore alerts for sites.

Generating Alert E-mails
Once the alert is created the system will check for a match every few minutes by looking for changes in the content database. When a change is found and it matches a subscription, an alert e-mail is generated. In the event that versioning is enabled and the user with the alert does not have access to the new version an alert will not be sent. The process that supports this on the server site is the timer service.

Back to Top 

Immediate Alerts
Each list basetype in WSS will have an alert template associated with it, which drives the UI behaviors, filtering, and e-mail content associated with a given alert.
Alert templates will be stored in the property bag for the list if it has been customized. This allows for the case where the list is provisioned from a basetype, and then columns are added or removed from it. 

The mechanism to support multiple alert templates per list is to have separate toolbar buttons for the list with the appropriate alert template passed in as a parameter on the subnew.aspx page.

SharePoint also supports multiple formats for the field to be rendered in e-mail:

1.      String: Including HTML. Text is truncated at 255 characters, which is set as an attribute in the template.
2.      Image: Image is rendered in the message, and included in the message.
3.      Link: The user is sent a hyperlink, for e-mail and HTTP links.

We collect multiple edits made to a given document/item in a quantum (5 minutes), and represent them as one change. This means, for example, that five simultaneous edits of a document are represented as one change in the immediate e-mail notification (instead of sending five separate e-mails). The e-mail will be formatted as a digest alert.

Back to Top 

Scheduled Alerts
There will be one daily digest sent per web. The sections in the mail are composed of each of the list types or items the user has created a daily alert for (individual sub-sections are defined in the formatting section for daily per alert template).  

• Multiple edits to the same item appear under one row in the section
  • changed information is aggregated with multiple asterisks in a given row if necessary.
• The Announcements digest section has the text  "Alert created by 'UserName'"
  • when creating digests for the user, if content is due to an alert created by another user, this text should be emitted below the digest section for that alert.
• The links for items take users to item display forms.
• The links for e-mail addresses or hyperlinks boot a new e-mail message or Internet Explorer
• The links for lists take users to the default view of a list (Note that SharePoint does not remember if an alert was specific to a view).
• The list of items is sorted by the list-name alphabetically (item alerts also appear with a list header).

Search based Alerts

Since this is already very good explained from my colleague Victor Butuza, I'll just add the link as reference here: "Search based Alerts"

Database Tables
To follow the trace of an alert process you can look into the content database tables on your SQL server.  

The tables that are most important when alerts are to be generated are the tables in the content database that start with the word event. In general terms, these tables are used as follows:

Back to Top 

How to troubleshoot alerts?

1. Enable Logging:     
   First of all go to Central Admin page and screw up your Diagnostic (ULS) logging to “verbose - All Errors”.
   
2. Repro the Issue:    
   Start repro your issue by creating an alert (immediate and/or scheduled) and note the timestamp you started as well as the timestamp you configured the scheduled alert to be fired.
   
3. Try to clear the cache (credits to Angelo Palma, who prosted this comment: Thank you!)
   "Go to C:\ Drive (make sure that all hidden folders are available) C:\ProgramData\Microsoft\SharePoint\Config\GUID\, sort by type and delete all files EXCEPT the cache.ini file.
   Once all files have been deleted edit the cache file by opening the file, change the value to 1 and save."

Perform some actions like changes, deletions, uploads etc. and note the timestamp of changes and wait about 5-10 minutes like this:

1.      On you SharePoint site log in as a regular user.
2.      Create several alerts both immediate and digest (on digest alerts set the delivery time to be the top of the next hour).
3.      Log in as a different user and modify, add, and delete some list items.
4.      Start Outlook on the client machine and view the alerts. You will have to wait until the top of the next hour to see the digest alerts (scheduled)

Back to Top 

Troubleshooting steps

Check if you have received any alert notifications at all!
Check now for specific alerts received or not received (Initial email, immediate alert email, scheduled alert email)

SQL Queries
Check on SQL Server if you can find your alert by using these queries for instance:

 1: /* you must gather the ListID from the EventCache Table */ 

 2: select * from [Content_DB].[dbo].[Eventlog] (nolock) where ListID = 'xxx'      

 3: /* if not having the ListID in place */

 4: select top 10 * from [Content_DB].[dbo].[Eventlog](nolock) order by id desc

 5:     

 6: select * from [Content_DB].[dbo].[ImmedSubscriptions] (nolock) where [UserEmail] = ‘User.email@domain.com’

 7:  

 8: /* if not looking for a specific user-email */

 9: select * from [Content_DB].[dbo].[ImmedSubscriptions] (nolock)

 10:  

 11: select * from [Content_DB].[dbo].[SchedSubscriptions] (nolock) where [UserEmail] = ‘User.email@domain.com’

 12:  

 13: /* if not looking for a specific user-email */

 14: select * from [Content_DB].[dbo].[SchedSubscriptions] (nolock)  

 15:   

 16: /* check documents corresponding to the affected list */ 

 17: select * from [Content_DB].[dbo].[EventCache] (nolock)order by [TimeLastModified]

 18:   

 19: select top 25 * from [Content_DB].[dbo].[EventSubsMatches] (nolock) order by id desc

 20:  

 Queries against the "Config_DB":

 1: select * from [Config_DB].[dbo].[objects] (nolock) where name like '%job-immediate-alerts%'

 2:  

 3: select * from [Config_DB].[dbo].[objects] (nolock) where properties like '%SPContentDatabase%'

Note: System Account is a special account (application pool) for which no security trimming happens. Hence this account will not get digest alerts.

Back to Top 

Check the Grid view for:

• Can you find your alerts in “ImmedScubscriptions / SchedScubscriptions” table?
• Do you have a related record in EventCache due to the Timestamp (TimeLastModified) you noted from your repro?
• Do you see the record for scheduled alert in the EventSubsMatches table?

Check also your ULS logs (maybe with ULS viewer) for entries like that:
(by default located at (C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\logs)

[…] …Begin invoke timer job Immediate Alerts, id … […]

[…] …AlertsJob loaded 9 of 9 event data records…  […]

[…] …AlertsJob loaded 5 of 5 subscription records…  […]

[…] …Alertsjob results for immediate delivery: 9 prematches, 9 passed filtering, 5 of 9 passed security trimming, 0…  […]

[…] …Alertsjob results for scheduled delivery: 0 prematches, …  […]

[…] …AlertsJob processed 0 daily notifications …  […]

[…] …AlertsJob processed 0 weekly notifications …  […]

[…] … …  […]

• Can you see any records indicating that your alerts are processed at all?

Check your Event log for suspicious errors related to the timeframe and probably containing phrases like “exception,  HResult errors, etc.”.

Check on your Server if the SharePoint services are running under the designated accounts.
(Maybe you can just retype the credentials and restart the service and run  "stsadm -o execadmsvcjobs" to trigger the immediate job processing)

Check if you are on an almost actual patch level as probably the cause of your issue already has been fixed in one of the last updates?

So if all those steps are don’t get you closer to the cause you are at least much more better prepared for the next step on calling Microsoft Support for assistance.
With all these troubleshooting steps done before you can provide all these actions to the support engineer and this may speed up the resolution as the Support Guys then can go directly and already narrowed down to a particular area with very deep troubleshooting steps for you!

So hopefully this post could help you somehow. I’ll be on updating this post whenever some new or deeper insights can be published to get this topic a bit more structured in depth and usage.

A small update, that I think might become Handy as well on Troubleshooting (from a recent comment on this post):

Comment from Chad, Mon, Dec 10 2012 10:41 PM         

(updated:  2012-12-11)

Back to Top 

Stay tuned ;-)

Steve Chen from daily business and the SharePoint Mysteries…