如何清除SQL 2005 数据库邮件队列中信息？

Question

邮件队列信息存储在系统表中，请问应该如何删除？（因目前邮件队列数据较多，阻塞了正常发送邮件）

Answer 1

Take look at sysmail_delete_mailitems_sp.

Answer 2

已经试过这个存储过程，它只能清除发出的信息，不能清除队列未发送出去的信息

Answer 3

Deletes e-mails of the type specified by sent_status which is varchar(8) with no default. Valid entries are sent, unsent, retrying, and failed.

Answer 4

上面存储过程的4个状态参数都试过了，执行完后，再执行sysmail_help_status_sp，其中的length值始终保持先前的数值不变

Answer 5

Did you check with sysmail_help_queue_sp?

Answer 6

对，一直用这条命令检查邮件队列

Answer 7

这样检查过了吗？“ sysmail_event_log” 和“Windows 应用程序事件日志”是否有相关信息。如果没有请参照下面的网址解决：
http://msdn.microsoft.com/zh-cn/library/ms189959.aspx

Answer 8

查过sysmail_event_log 和Windows 应用程序事件日志，都有下面的错误提示（以前正常，出现下面错误后就不能发邮件了，后来更换了服务器，正常了两天，也出现这种错误提示，接着也不能发邮件了）：
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: There was an error on the connection. Reason: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached., connection parameters: Server Name: DB02, Database Name: msdb
Data: System.Collections.ListDictionaryInternal
TargetSite: Void OpenConnection(Microsoft.SqlServer.Management.Common.SqlConnectionInfo)
HelpLink: NULL
Source: DatabaseMailEngine

StackTrace Information
===================
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName, String dbName, String userName, String password)
   at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel)

2) Exception Information
===================
Exception Type: System.InvalidOperationException
Message: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
Data: System.Collections.ListDictionaryInternal
TargetSite: System.Data.ProviderBase.DbConnectionInternal GetConnection(System.Data.Common.DbConnection)
HelpLink: NULL
Source: System.Data

StackTrace Information
===================
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)

Answer 9

已参考http://msdn.microsoft.com/zh-cn/library/ms189959.aspx

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';   返回1

EXECUTE dbo.sysmail_start_sp 可以启动服务
但执行EXECUTE dbo.sysmail_help_status_sp， 状态始终是inactive

Answer 10

我这边发送邮件正常的，等空闲一段时间之后状态也会是inactive的，关键是队列长度：正常的是发送完的为0。

Answer 11

最好能换下邮件服务器试试看，很有可能源头是邮件服务器的处理有问题。

Answer 12

邮件队列长度6千多条，每发送一次，队列长度会增加1 ，并且状态始终不变（正常发送邮件时会出现RECEIVES_OCCURRING）, 
看来是邮件服务器的问题较大