You have been tasked to delete a number of Database Mail messages that have been sent. You need to delete all the emails that were sent more than one month ago. Which Transact-SQL statements should…

Question

Options

• ADECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
• BDECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
• CDECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())
• DDECLARE @OneMonthAgo datetime = DATEADD(mm,-1,GETDATE())

• A
  5% (2)
• B
  12% (5)
• C
  2% (1)
• D
  81% (35)

Explanation

sysmail_delete_mailitems_sp [ [ @sent_before = ] 'sent_before' ] [ , [ @sent_status = ] 'sent_status' ] Permanently deletes e-mail messages from the Database Mail internal tables. [ @sent_before= ] 'sent_before' Deletes e-mails up to the date and time provided as the sent_before argument. sent_before is datetime with NULL as default. NULL indicates all dates. [ @sent_status= ] 'sent_status' Deletes e-mails of the type specified by sent_status. sent_status is varchar(8) with no default. Valid entries are sent, unsent, retrying, and failed. NULL indicates all statuses. Database Mail messages and their attachments are stored in the msdb database. Messages should be periodically deleted to prevent msdb from growing larger than expected and to comply with your organizations document retention program. Use the sysmail_delete_mailitems_sp stored procedure to permanently delete email messages from the Database Mail tables. An optional argument allows you to delete only older e- mails by providing a date and time. E-mails older than that argument will be deleted. Another optional argument allows you to delete only e-mails of a certain type, specified as the sent_status argument. You must provide an argument either for @sent_before or @sent_status. To delete all messages, use @sent_before = getdate().

No community discussion yet for this question.