You are the SQL administrator for your company. Two SQL Server 2008 computers, named SQL1 and SQL2 ,have been deployed.
Several reports run on a daily basis and are e-mailed to the appropriate personnel using Database Mail. The msdb database is growing much larger than expected, and you discover that this is due to the volume of e-mailmessages generated by Database Mail. You need to delete all e-mail messages that are more than 30 days oldfrom the msdb database.
You decide to create a job that will automatically run once each week. Which system stored procedure should the job execute?
A.
sysmail_delete_mailitems_sp
B.
sysmail_delete_log_sp
C.
sysmail_configure_sp
D.
sp_processmail
Explanation:
The job should execute the sysmail_delete_mailitems_sp system stored procedure. The following syntax isused:
sysmail_delete_mailitems_sp [ [ @sent_before = ] ‘sent_before’ ]
[ , [ @sent_status = ] ‘sent_status’ ]If you execute this procedure without specifying the @sent_before or @sent_status parameter, all e-mails in theDatabase Mail system are deleted. If you execute this procedure with the @sent_before parameter, all e-mailmessages older than the specified date are deleted. The following example executes the sysmail_delete_mailitems_sp
system stored procedure to delete all e-mail messages sent before May 30,2008:
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
@sent_before = ‘May 30, 2008’;
GOIf you execute this procedure with the @sent_status parameter, you can delete e-mail messages based on theirstatus, such as sent , unsent , retrying , or failed .
The following example executes the sysmail_delete_mailitems_sp system stored procedure to delete all sent e-mail messages:
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp
@sent_status = ‘sent’;
GOThe job should not execute the sysmail_delete_log_sp system stored procedure. This system stored proceduredeletes events from the Database Mail log, not e-mail messages. The job should not execute the sysmail_configure_sp system stored procedure. This system stored procedurechanges the configuration settings for Database Mail, such as limiting the size of e-mail attachments orconfiguring the logging level. It does not delete e-mail messages. The job should not execute the sp_processmail
system stored procedure. This system stored procedureprocesses incoming e-mail messages from SQL Server’s Inbox. It does not delete e-mail messages.Objective:
Installing and Configuring SQL Server 2008Sub-Objective:
Implement database mail.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Stored Procedures (Transact-SQL) > Database Mail and SQL Mail Stored Procedures(Transact-SQL) > sysmail_delete_mailitems_sp (Transact-SQL)