Email database object maintenance
The following table lists the Email database objects.
Email database tables
dbo.SendEmailHistory
View of current and completed tasks. The tables is useful for tracking current and historical activity.
dbo.Schedule
Defines the task schedule.
dbo.SendEmailTask
Contains a full description of a task that is usable only by send email tasks.
dbo.ScheduleTask
Links tasks to schedules by defining an N:N relationship between rows in the Schedule table and rows in the SendEmailTask table. When a task in the Schedule table comes due, one or more tasks can be performed. A single task can be initiated by multiple Schedules.
Email database indexes
IX_SendEmailHistory_ScheduleTaskId
Non-clustered index on dbo.SendEmailHistory.ScheduleTaskId.
IX_SendEmailHistory_BatchStatus
Non-clustered index on dbo.SendEmailHistory.BatchStatus.
IX_Schedule_EndTime
Non-clustered index on dbo.Schedule.EndTime.
IX_ScheduleTask_TaskId
Non-clustered index on dbo.ScheduleTask.TaskId.
IX_ScheduleTask_ScheduleId
Non-clustered index on dbo.ScheduleTask.ScheduleId.
IX_ScheduleTask_Status
Non-clustered index on dbo.ScheduleTask.Status.
IX_ScheduleTask_NextDueTime
Non-clustered index on dbo.ScheduleTask.NextDueTime.
IX_ScheduleTask_LastKeepAliveTime
Non-clustered index on dbo.ScheduleTask.LastKeepAliveTime.
Email database procedures
dbo.EmailInsertSendEmailHistory
PaticipantEmailer inserts a new record into the table when a task is started.
dbo.EmailUpdateSendEmailHistory
ParticipantEmailer updates the record (BatchStatus, BatchEndTime, SuccessfulEmailCount, FailedEmailCount) when the task is finishes, fails, or the task is not started and is disposed.
dbo.EmailGetAllSendEmailTasks
Used by IEmailer.Tasks interface to retrieve all send email schedule tasks.
dbo.EmailGetSendTask
Used by IEmailer.GetNextTask, to retrieve a next send email schedule task. The procedure is also used to validate a task from IEmailer.Tasks (when a user starts a task).
dbo.SchedUpdateScheduleTask
ParticipantsEmailer updates the dbo.ScheduleTask table (Status, ExecuteCount, NextDueTime, LastKeepAliveTime) when a scheduled task is finishes or fails.
dbo.SchedGetScheduleTask
Used by the IEmailTask.Dispose interface. The procedure is called to change the task status when a task is not started, when a user calls Dispose, or when the task is released by GC.
dbo.EmailCheckForAbandonedAndCompletedTasks
1. Called by ParticipantEmailer when retrieving the next task. The procedure checks for stuck records, or completed schedule task.
2. Reset stuck SendEmailHistory records for and update the batch status totsAbandoned.
Update the ScheduleTask from tsWaiting to tsComplete when theEndTime is reached.)
dbo.EmailUpdateSendStatus
When a task is started from, ParticipantEmailer the procedure updates the database at regular intervals. The procedure updates SuccessfulEmailCount, the SendEmailHistory table's FailedEmailCount, and the ScheduleTask table's LastKeepAliveTime.
dbo.EmailScheduleTaskUpdate
Adds or edits the Email activity's schedule task.
dbo.SchedGetScheduleTask
Retrieves the Email activity's schedule email record (for editing).
dbo.EmailGetJobStatus
Retrieves the current user interface's email job status.
Notes
The dbo.Schedule and dbo.ScheduleTask tables are designed to support the scheduling of emails and other schedule oriented activities. The dbo.SendEmailTask and dbo.SendEmailHistory tables are specific to the Email activity.
The email sending and task status are stored in dbo.SendEmailHistory.BatchStatus and dbo.ScheduleTask.Status indexes. The email sending history records are stored in the dbo.SendEmailHistory table.
It is possible to use the scheduling tables to check the current task status and history. When a project is deleted, the related schedule records are also deleted. For more information, see
mrInterviewData database stored procedures.
The following SQL statement can be used to delete old records (for example, records from the previous year):
-------------------------
DECLARE @DelYear INT
SET @DelYear = 1
DECLARE @DeletedScheduleTask TABLE
(
ScheduleTaskId INT
)
INSERT INTO @DeletedScheduleTask
SELECT DISTINCT ScheduleTaskId from dbo.SendEmailHistory WHERE BatchEndTime < DATEADD(year, -@DelYear, GETDATE())
-- Delete SendEmailHistory
DELETE FROM dbo.SendEmailHistory WHERE BatchEndTime < DATEADD(year, -@DelYear, GETDATE())
-- Delete ScheduleTask
DELETE FROM ST FROM dbo.ScheduleTask as ST
INNER JOIN @DeletedScheduleTask as DST
ON ST.Id = DST.ScheduleTaskId
WHERE ST.LastKeepAliveTime < DATEADD(year, -@DelYear, GETDATE())
-- Delete Schedule
DELETE FROM dbo.Schedule WHERE EndTime < DATEADD(year, -@DelYear, GETDATE())
----------------------------
See also