|
|
Sharepoint 2007 task notification alert emails not working
"Where are my alert emails!" I hear you cry. I've been repeating these same words for a number of days now and I've finally managed to get them working so I thought I'd share my solution.
Scenario:
I've been working with a MOSS site which has three environments; development, staging and live. We have a number of custom workflows which were created using Workflow Foundation and InfoPath for the forms. Each of the workflow tasks should automatically send out a task notification email whenever the task is reassigned to a different person. This has always worked fine on staging and live environments, but I've never seen them working in the development environment. Interestingly all the standard scheduled alert emails were working fine (ie. if you were to subscribe to a list), it was just the task notifications from the workflows which were not.
Solution:
First thing I did was go through the usual process of checking the log files and windows event viewer for errors which yielded no significant results. Next thing I checked were the email settings for the site in Central Admin which again all looked fine. The development site is using local SMTP, so I checked if the emails were getting generated in the 'DROP' folder in the mailroot. The scheduled alert emails were getting generated in here fine, but the task notifications were not. I also checked that the Timer service was running correctly and under the right account in the windows Services, which it was.
Next thing I checked were the database tables, after I read about the same problem in this newsgroup posting. The following four tables in the content database contain entries related to the alert emails:
ImmedSubscriptions (Stores the alerts for emails that are sent immediately when changes occur)
SchedSubscriptions (Stores daily or weekly scheduled alerts)
EventLog (This table contains events for which only non-immediate alerts exist)
EventCache (This table contains a list of site events for which users have requested alerts. WSS inserts events into this table as they occur)
I checked in these table to see if information about the alerts were being inserted and discovered that in the ImmedSubscriptions and EventLog tables there were actually entries which were related to the live server, as I believe the content database had been restored from a live copy a while ago, and the references obviously had not been updated automatically. So I cleared out each each one of these tables and re-tested the workflow. This made a bit of progress as I could now see that the alert information was getting inserted correctly into the EventCache and EventLog tables. However the ImmedSubscriptions table was still not receiving information about the alert.
After some frustrated iisresets and restarts of the timer job service, I was still having no luck whatsoever at getting these alert emails working so I reverted back to trusty Google for some more answers. I found this blog, which although not directly related, provided the winning answer. Updating the alert templates. After running the following stsadm command on the development machine, the task notification alert emails automagically started working again woohoo!
stsadm -o updatealerttemplates -url http://testserver -f "c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\XML\alerttemplates.xml" -LCID 2057
So I can assume that the problem transpired from a restore of the content databased from a different server which somewhere along the line maintained some references to the original server. A clear out of the relevant databases and re-registering the alert templates seemed to solve the problem for me.
Hopefully this comes in handy for somebody else, as I spent many a frustrated hour over this one :)
---------------------------------------------------
Others reference:
Hi all,
I’m currently having an issue with Sharepoint Portal Server 2007 and alerts.
I know the alerts are set up and working correctly because if we create a new
site collection/sites and add alerts to list under these, they work as
advertised.
The issue I am having is after we have imported a site from a dev/test
Sharepoint environment and then add or modify alerts for this site. I had to
move an entire site collection from a development environment running
Sharepoint 2007 Beta2TR to a production environment running the RTM version.
I accomplished this by just backing up the content database, restoring it to
the production server, and then attaching it to a web application using the
stsadmin – attachcontentdb command. This worked well for everything (lists,
content, etc etc) but alas the alerts failed to work at all after this move.
The only thing I can see in the logs that seems relevant is: 0x1E90 Windows
SharePoint Services General 75ko High Unknown
Change Microsoft.SharePoint.SPChangeAlert
If I create a new list in this restored site, we get alerts from that, but
we receive nothing from the lists that were associated with the restore. Even
deleting and re-adding the alerts fails to kick them off.
If anyone has any ideas, or experience like this, I would be forever
grateful! I would even be happy if there is a way to reset and wipe alerts
for a given site collection and start them again.
Cheers,
Matt.
date: Wed, 14 Feb 2007 20:35:03 -0800 author: Matt
RE: Sharepoint 2007 alerts failing after database restore
Matt,
Please check the following
1. Are you receving a confirmation that you've got subscribed for the alerts ?
2. Alerts are based on SMTP, check whether you have configured the proper
SMTP server and check whether MOSS 2007 server has access to SMTP server
3. If you have anti-virus software on the server, check whether it is
blocking the bulk mail functionality, becuase alerts are going to be sent as
bulk mails
4. Please check whether the events are turned-on the MOSS 2007 server, else
explicity the turn on the events in the server
5.Please check whether the following tables in MOSS 2007 database have
entries related to your alerts
EventLog table
EventCache table
SchedSubscription table
ImmedSubscription table
6. Verify all of your accounts (Service LOG ON accounts, APP Pool accounts
and DataBase accounts)
whether it has proper access
7. Verify whether the indexing and Crawling has happened properly.
8. Check the gatherer log and see whether there are any errors
Please update me after all the checks
As far as i know, there is no way to turn-off/turn-off/re-start the alerts
at Site Collection level
--
Thanks & Regards,
Mark Nelson
"Matt" wrote:
> Hi all,
>
> I’m currently having an issue with Sharepoint Portal Server 2007 and alerts.
> I know the alerts are set up and working correctly because if we create a new
> site collection/sites and add alerts to list under these, they work as
> advertised.
>
> The issue I am having is after we have imported a site from a dev/test
> Sharepoint environment and then add or modify alerts for this site. I had to
> move an entire site collection from a development environment running
> Sharepoint 2007 Beta2TR to a production environment running the RTM version.
> I accomplished this by just backing up the content database, restoring it to
> the production server, and then attaching it to a web application using the
> stsadmin – attachcontentdb command. This worked well for everything (lists,
> content, etc etc) but alas the alerts failed to work at all after this move.
> The only thing I can see in the logs that seems relevant is: 0x1E90 Windows
> SharePoint Services General 75ko High Unknown
> Change Microsoft.SharePoint.SPChangeAlert
>
> If I create a new list in this restored site, we get alerts from that, but
> we receive nothing from the lists that were associated with the restore. Even
> deleting and re-adding the alerts fails to kick them off.
>
> If anyone has any ideas, or experience like this, I would be forever
> grateful! I would even be happy if there is a way to reset and wipe alerts
> for a given site collection and start them again.
>
> Cheers,
> Matt.
>
date: Wed, 14 Feb 2007 23:10:08 -0800 author: Mark Nelson
RE: Sharepoint 2007 alerts failing after database restore
Hi Mark,
Thanks for your reply. I have outlined your questions below (and solved
based on your suggestions).
1. Yes, we are receiving the confirmation of subscription to the list we add
the alert to – just no immediate alerts are received.
2. We have no problems sending any other type of email from this site and
have ensured the smtp server details are set up correctly.
3. Will follow up with the security team re this (apparently not an issue).
4. Yes, Events are turned on and being logged.
5. **This was the issue**
The alerts in the ImmedSubscription had the wrong details for the alerts
ported from the development server for some reason. Wiping out everybody’s
alerts in this list and the SchedSubscription table seemed to do the ‘reset’.
The result was that all alerts migrated across from dev were lost but I can
live with getting people to re-enter them as it was a small quantity at this
stage.
The alerts were not being placed in the EventLog either until they were
wiped and re-added.
6. I’m assuming all the accounts have the right access because any new sites
that are created have alerts working flawlessly.
7. Full indexing is functioning once per day and incremental is churning
through every 10 mins.
8. I cannot see any gather log errors or warning relating to the crawls
around the time the alerts should be processed.
Thanks for all the ideas – it helped me solve the problem in record time
after spending a fair bit trying to research!
Cheers,
Matt.
"Mark Nelson" wrote:
> Matt,
>
> Please check the following
>
> 1. Are you receving a confirmation that you've got subscribed for the alerts ?
> 2. Alerts are based on SMTP, check whether you have configured the proper
> SMTP server and check whether MOSS 2007 server has access to SMTP server
> 3. If you have anti-virus software on the server, check whether it is
> blocking the bulk mail functionality, becuase alerts are going to be sent as
> bulk mails
> 4. Please check whether the events are turned-on the MOSS 2007 server, else
> explicity the turn on the events in the server
> 5.Please check whether the following tables in MOSS 2007 database have
> entries related to your alerts
> EventLog table
> EventCache table
> SchedSubscription table
> ImmedSubscription table
>
> 6. Verify all of your accounts (Service LOG ON accounts, APP Pool accounts
> and DataBase accounts)
> whether it has proper access
> 7. Verify whether the indexing and Crawling has happened properly.
> 8. Check the gatherer log and see whether there are any errors
>
> Please update me after all the checks
>
> As far as i know, there is no way to turn-off/turn-off/re-start the alerts
> at Site Collection level
> --
> Thanks & Regards,
> Mark Nelson
>
>
> "Matt" wrote:
>
> > Hi all,
> >
> > I’m currently having an issue with Sharepoint Portal Server 2007 and alerts.
> > I know the alerts are set up and working correctly because if we create a new
> > site collection/sites and add alerts to list under these, they work as
> > advertised.
> >
> > The issue I am having is after we have imported a site from a dev/test
> > Sharepoint environment and then add or modify alerts for this site. I had to
> > move an entire site collection from a development environment running
> > Sharepoint 2007 Beta2TR to a production environment running the RTM version.
> > I accomplished this by just backing up the content database, restoring it to
> > the production server, and then attaching it to a web application using the
> > stsadmin – attachcontentdb command. This worked well for everything (lists,
> > content, etc etc) but alas the alerts failed to work at all after this move.
> > The only thing I can see in the logs that seems relevant is: 0x1E90 Windows
> > SharePoint Services General 75ko High Unknown
> > Change Microsoft.SharePoint.SPChangeAlert
> >
> > If I create a new list in this restored site, we get alerts from that, but
> > we receive nothing from the lists that were associated with the restore. Even
> > deleting and re-adding the alerts fails to kick them off.
> >
> > If anyone has any ideas, or experience like this, I would be forever
> > grateful! I would even be happy if there is a way to reset and wipe alerts
> > for a given site collection and start them again.
> >
> > Cheers,
> > Matt.
> >
RE: Sharepoint 2007 alerts failing after database restore
Hi,
I have the same problem going on. Everything was working fine till yesterday,
I did some database user changes for running some sharepoint accessing code
from windows service. And since yesterday I have not had any entry in the
EventLog table. I do not know what to do.
Please help.I need to fix it real quick.
please please please.
http://www.ureader.com/msg/12289298.aspx
date: Wed, 21 Nov 2007 12:29:32 +0800 author: tina s
RE: Sharepoint 2007 alerts failing after database restore
I am having the same problem and I am finding that MOSS is not writing to
the event log table in the database3, how do you turn on events?
http://www.ureader.com/msg/12289298.aspx
date: Fri, 4 Jan 2008 23:02:44 +0800 author: alan parsins
RE: Sharepoint 2007 alerts failing after database restore
It looks like you have tried several of my recommendations already per the
previous posts but I think there are a few other avenues to explore on my
blog:
http://www.thesug.org/blogs/annw/Lists/Posts/ViewPost.aspx?ID=3
http://www.ureader.com/msg/12289298.aspx
RE: Sharepoint 2007 alerts failing after database restore
hi
i have a new moss 2007 server
subsequent alerts are not going
ther is no enrty in SchedSubscription
what i have to do
plse help me
http://www.ureader.com/msg/12289298.aspx
date: Thu, 21 Aug 2008 18:08:34 +0800 author: bibin peenikkal
RE: Sharepoint 2007 alerts failing after database restore
For paragraph 5, i am unclear how you removed the older alerts. when i
check my sql db and look at the immedsubscription table, i see about 7 names
listed for the site having the alert issue on one list. all others work
fine.
http://www.ureader.com/msg/12289298.aspx
------------------
Reference 2
The alerts are working, but for some reason the " Send e-mail when ownership is assigned" on a task list is not working.
A task is assigned to a user however Immediate-alerts are not sending the emails. I tried with the below commands
stsadm -o updatealerttemplates -url http://testserver/ -f "c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\XML\alerttemplates.xml" -LCID 2057
stsadm -o setproperty -pn job-immediate-alerts -pv "Every 2 minutes between 0 and 59" -url http://hulmossteam:25000/
also, turned off and turned on the "Send e-mail when ownership is assigned? " setting but still not able to fix the issue.
any thoughts ?
Update:
Deleted the existing Tasks list and created a new Tasks List still it is not working...
I could see the below entries are in the "dbo.ImmedSubscriptions" Table against that site-collection
UserEmail=
ListURL = Lists/Tasks
AlertTitle = System
AlertType = 536870912
AlertTemplateName = SPAlertTemplateType.AssignedToNotification
I have restarted the Timer job but still no luck, subscription emails are working so I guess SMTP is configured correctly.
I did not do an IISRESET, is that mandatory ?
-----
The first thing to realize is that SharePoint has basically two methods of sending emails
a) In response to an event on a page load/postback - such as
- The Alert Me confirmation message or
- Assigning a task (the one you are missing)
- Adding someone to a site.
b) From the the SharePoint timer service which includes all the Alert Me type emails including the "Immediate" one (its not really immediate, its every 2 minutes by default.)
Your missing emails are coming from a) and not b) - so I don't think mucking around with the alerttemplates.xml file or the job-immediate-alerts setting is going to help.
The first things to discount
- SPAM filters
- Incorrect email addresses in the person you're assigning a task to (don't assume because its right in AD its right in SharePoint - click on the name in the task list and verify)
Then I would check are you getting the "Alert Me Confirmation" emails when you sign that same person up to an alert.
If you DON'T get that then I would look at anti-virus blocking smtp emails from the W3P worker process (the timer service runs in a different process).
If you do then gawd only knows - time for Microsoft PS?
I've put together a troubleshooting guide for SharePoint Alerts - but I don't think its going to help much in your case.
-----
Checked the timerlock table on the content database and it was running on the Inactive SharePoint Server. so, Stopped the Timer service temporarily on that server which moved the timerlock to the active Server.
Steps to resolve the issue:
- Make sure you have SharePoint already configured for outgoing emails.
- Make sure that you have alerts for the document library turned on if you are testing with the document library.
- Run this command from C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN: stsadm -o updatealerttemplates -filename "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\XML\customalerttemplates.xml" -url
- Run this command: stsadm -o setproperty -pn job-immediate-alerts -pv "every 1 minutes"
- Run this command from the command prompt: iisreset
- Run this command from the command prompt: services.msc
- From the services window, restart the Windows SharePoint Services Timer.
--------------------------------------------------------------------
Reference 3
---------------------------------------------------------------------
Alerts in SharePoint (Troubleshooting MOSS/WSS)
This will be the first post about “troubleshooting MOSS/WSS” on a series, defined by topics and Issues. Another post following this series has been published now here: "Alerts in SharePoint Part II (Troubleshooting MOSS/WSS)"(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
- Database tables
- How to troubleshoot alerts?
- Troubleshooting steps
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\…) 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 –pn 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. (经过排查，发现视乎只有当前某两个content DB中的site存在alert 出问题的情况，因此通过最后的KB用migrate site from one content DB to the others content DB)
- 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).
Event
| Verbose
| Exception logging only
| Alerts created
| X
|
| Alert modified
| X
|
| Alert deleted
| X
|
| Alert Filter/Formatting could not be applied
| X
| X
| Alert status was modified
| X
|
| end e-mail notification successfully
| X
|
| SMTP server connection not available
| X
| X
| Recipient not found (mailbox error)
| X
| X
| Filter incorrect, user alert and filter deleted
| X
| X
| Begin matching in SQL
| X
|
| Complete matching in SQL
| X
|
| Begin XML matching
| X
|
| Complete XML matching
| X
|
| Begin ACL trimming
| X
|
| End ACL trimming
| X
|
|
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).
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:
ImmedSubscriptions
| records the immediate alerts settings
|
SchedSubscriptions
| records the scheduled alerts settings
| | EventCache | Change Log: records all events in SharePoint, so either the alert changes, stores events used in immediate alerts along with an id that is used in eventbatches. The eventtype is also stored in this table.
|
EventLog
| This table contains events for which only non-immediate alerts exist and stores events used for digest alerts;
Keeps processed alerts for last 7 days along with EventData which was present in EventCache before alert got processed
| | EventBatches | Keeps track of the most recent event that has been processed for alerting, Keeps the last change log ID and time stamp from EventCache after which new processing should start when “Immediate Alerts” job runs
| | EventSubsMatches | records the timestamp when a scheduled alert has to be processed by the timerjob and used as a temp table during alert processing;
Matches the change log ID with the Subscription ID (alert ID) from SchedSubscriptions table and permission result.
Entry in this table gets flushed once scheduled alerts is sent for that subscription
| | EventReceivers | Used for custom event handlers
|
TimerLock
| records the server that processes the timerjobs
| | |
Back to Top
How to troubleshoot alerts?
- Enable Logging:
First of all go to Central Admin page and screw up your Diagnostic (ULS) logging to “verbose - All Errors”.
- 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.
- 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.
(last update: 2010-11-24)
Back to Top
Stay tuned ;-)
Steve Chen from daily business and the SharePoint Mysteries…
KB Article, Trouble Shooting, Migration, WSSv3, MOSS, Links, Alerts, Issue
-----
Move site collections to a new database (split a content database) (Windows SharePoint Services 3.0)
Updated: 2009-05-14
Some site collections within a database that hosts multiple site collections are growing faster than expected, and you expect the database to approach its size limit quickly. You can split the database to move the growing site collections to another database or to another server.
Split a content database
You can use this procedure to split a content database that is associated with your site collections. The process of moving a site collection between databases consists of exporting the site collection URL data to a file and then importing the data to a new database.
Important:
We recommend that you install the April Cumulative Update before you split content databases. This update fixes known issues with the Stsadm mergecontentdbs operation. For more information, see Deploy software updates for Windows SharePoint Services 3.0.
Before you use the Stsadm mergecontentdbs operation, make sure that there is free space available on the hard disk that is equal to at least three times the size of the source site collection. To determine the size of the site collection, refer to the StorageUsedMB attribute of the Stsadm enumsites operation in step 2 of the following procedure.
To complete the following procedure, you must be a member of both the Farm Administrators group and the Administrators group on the local computer. You must have Full Control permission for any site collection that needs to be moved. The account that you use to perform this procedure must be a member of the db_ownerfixed database role in SQL Server.
Split a content database (move a site collection to a different database)
- At a command prompt on the drive where SharePoint Products and Technologies is installed, change to the following directory:
%COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\12\Bin
- Type the following command, and then press ENTER:
Stsadm -o enumsites -url <URL> > <path/file name>.xml
where url is the address of the Web application that contains the site collection that you want to move, and path/file name is the name of the XML file that you want to create with the site collection data.
For more information, see Enumsites: Stsadm operation (Windows SharePoint Services).
The previous step creates an XML file that contains all of the site collection URLs in the current database.
- Open the XML file that you created in a text editing application. If there are any URLs for site collections that you do not want to move, be sure to delete them from the file. The only URLs that should remain in the XML file should be for the site collections that you want to move.
Note:
There is no need to change the site count or any of the other site collection information in the file. Only the URLs are relevant to this procedure.
- Type the following command, and then press ENTER:
Stsadm -o mergecontentdbs -url <URL> -sourcedatabasename <database name> -destinationdatabasename <database name> -operation 3 -filename <file name>
where url is the address of the Web application that contains the site collection that you want; sourcedatabasename is the name of the database that you want to move the site collection from; destinationdatabasename is the name of the database that you want to move the site collection to; operation 3 is the “Read from file” operation; and file name is the name of the file that contains the site collection URLs that you want to move.
For more information, see Mergecontentdbs: Stsadm operation (Windows SharePoint Services).
- Restart IIS by typing the following command, and then pressing ENTER:
iisreset /noforce
------------update----------
和微软的兄弟一起在code层面分析了原因，找到了rootcause，呵呵解决，不过教训深刻呀
Problem description
==============
在SharePoint 2007系统环境下，用户订阅的Alert mail不发送。
Cause
==============
这个问题是由于之前单独Truncate过EventCache表，而没有清理EventBaches表，导致EventCache中ID重新计数，而EventBaches表中还保留以前系统中已经发过的ID数，所以，在目前情况下，直到Alert数量增长到之前数量之前，Alert邮件均不会被发送。如果用delete方式删除表中的记录，自增长的ID就不会重新计数，这样问题就不会产生了。
顺便说一下，本例子删除Eventcache表中数据的起始原因是，有时候MOSS会发疯似的发送之前因为某些异常block的邮件内容，一下子涌出几万封邮件，将exchange server和用户邮箱弄垮。。在eventcache表中acl is not null的数据条目如果非常大话，可以预见到提醒邮件的海啸以及exchange管理员和用户的咆哮也不远了。。。。。。。。。本意是解决问题，不当心给自己添了更多的麻烦。。。。。。郁闷一记。。。。。。。。。。。
Resoultion
==============
1. 检查订阅初始通知邮件有没有发送，发现初始邮件发送正常。
2. 检查管理中心中Timer Job任务执行状态，所有Change Log均正常，有一个 Immediate Alerts发送错误，不过错误发生时间比较早。之后重启Timer服务，状态全部回复正常，但邮件发送依然有问题。
3. 用命令行“stsadm.exe -o getproperty -url <http://problemsite/> -pn alerts-enabled”检查alerts-enabled状态，结果正常。
4. 用命令行“stsadm.exe -o setproperty -url <<http://problemsite/>> -pn job-immediate-alerts -pv "every 5 minutes between 0 and 59””设置执行时间间隔，问题依旧。
5. 用命令行“stsadm -o updatealerttemplates -url http://localhost/ -f C:\Alerttemplates.xml”更新Alert定义，问题依旧。
6. 在数据库端执行“Select * from EventCache where ACL is not null”，发现时间记录添加正常。
7. 检查所有排查步骤的verbose ULs logs，未发现任何异常和报错信息，但是发现系统Timer从EventCache没有load任何数据。
… OWSTIMER.EXE (0x0E3C) 0x0E9C … 95lc Verbose AlertsJob loaded 0 of 0 event data records
|
8. 之后查询源代码，发现系统此步骤中用名为“proc_GetEventDataAndSubscriptionFilers”的存储过程来读取数据。
9. 用当前数据库中数据和“proc_GetEventDataAndSubscriptionFilers”做比对，最后发现此存储过程还调用了“EventBaches”表存储一个标志位，记录已经发送过的Alert的ID（EventCache表中的ID--这个ID用delete的方式不会重新开始计算，用truncate的方式，ID会从1开始重新计数，所以一切看起来如此正常，但是就是发不出邮件。。。。。。。。。。。）
10. 由此判断是由于EventCache和EventBaches数据不一致产生的问题。
解决方案
==============
对EventBaches数据表进行Truncate操作，然后邮件会全部正常发送。
注：不建议用户自行对数据库表进行操作，因为EventCache表中的数据和EventBaches表相互关联，只对其中一个执行truncate操作，会出现数据不统一而产生Alert邮件发送异常的情况。
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2015-9-28 14:14:21
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡