Symptoms

• Say that we have page A and page B. For page A, watchers received mail notification when that page is edited or commented. For page B, no mail notification is sent out/received. 
• Confluence is configured to use MS SQL

Diagnosis

Enable mail debug logging by including -Dmail.debug=true parameter to JAVA_OPTS. See Configuring System Properties. This parameter will print out logging email protocol details in catalina.log. Check if there is any email triggered when making changes to the affected page. If no email is triggered, run these queries against the database:

SELECT is_read_committed_snapshot_on FROM
sys.databases WHERE name= 'CONFLUENCE';

SELECT DATABASEPROPERTYEX('CONFLUENCE', 'Collation') SQLCollation;

Cause

MS SQL server is not configured as Read Committed and the database collation is not set to case sensitive.

Resolution

As per the SQL Server documentation, Confluence requires MS SQL server database to be:

1. Read committed
2. Case sensitive collation

To fix this:

1. Shut down Confluence
2. Create a backup of your Confluence database
3. Execute the following query against the database:

ALTER DATABASE <database_name>
   SET READ_COMMITTED_SNAPSHOT ON
   WITH ROLLBACK IMMEDIATE;
 
ALTER DATABASE <database_name> COLLATE SQL_Latin1_General_CP1_CS_AS;

4. Start Confluence.