In a non-clustered environment files are written to a directory ..\NotifServerDumpFilesDir. These corelate to the TS_THREADID seen throughout the Notification tables. These are used in case of a system failure to recover the Mail Services components in case of being mid- process.
During normal activity, when a scheduled report is being processed by the notification server a value will be placed in the TS_THREADID column of the TS_NOTIFICATIONEVENTS table for that particular notification event. If something happens during this process that causes the report to fail or the notification services is terminated unexpectedly, then the TS_THREADID column is left with a non-NULL value which causes the scheduled report to be skipped next time. We have also seen this happen during the upgrade process.
org.springframework.dao.DataIntegrityViolationException: could not execute update query; SQL [update TS_CHANGEACTIONS set TS_THREADID =null where TS_ID in (? , ? , ? , ?
After doing some research it seems like any row in this table where the ts_threadid is not null is seen to be being processed currently by the notification server so it's assumed it's being worked on. The problem is that certain items would never leave this table and certain notifications would simply stop firing.
This can happen if items were archived in a version prior to SBM 10.1 which did not have the TS_THREADID column in the TV_TS_CHANGEACTIONS table. To get around the issue, running a new archive on at least one item will create that missing column and allow all previously archived items to be restored without error.
In some instances, SBM 10.1 or newer will not create the new columns in the notification server tables (TS_NOTIFICATIONEVENTS and TS_NOTIFICATIONMESSAGES) which will cause the notification server to fail with errors regarding columns such as TS_DELAYED or TS_THREADID .
For example: select * from TS_NOTIFICATIONEVENTS where TS_THREADID is not null and TS_TIME < 1416355200 and TS_TIME <> 0 select * from TS_NOTIFICATIONEVENTS where TS_THREADID is not null and TS_TIME = 0 and TS_WAITSTATUS = 0 and TS_STARTDATE < 1416355200
The workaround currently is to remove these events every 5 minutes by deleting the records returned by the SQL script select * from TS_NOTIFICATIONEVENTS where TS_WAITSTATUS=2 and TS_THREADID is NULL; This is fixed in 11.3.1 and above.
