Which system stored procedure should the job execute?

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?

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’;
GO

If 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’;
GO

The 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 2008

Sub-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)



Leave a Reply 0

Your email address will not be published. Required fields are marked *