In SQL Server, you can use the sysmail_help_queue_sp stored procedure on the msdb database to see how many mail items are in the queue, the status of the queue, and when it was last activated.

There are two types of queue: the mail queue, and the status queue.

You can also return account info based on the queue type.

Return All Items in the Queue

To return all items from the queue, simply execute the stored procedure without any parameters.

EXEC msdb.dbo.sysmail_help_queue_sp;

Result (using vertical output):

-[ RECORD 1 ]-------------------------
queue_type             | mail
length                 | 0
state                  | INACTIVE
last_empty_rowset_time | 2020-08-25 22:25:02.637
last_activated_time    | 2020-08-25 22:25:02.637
-[ RECORD 2 ]-------------------------
queue_type             | status
length                 | 0
state                  | INACTIVE
last_empty_rowset_time | 2020-08-25 22:25:02.637
last_activated_time    | 2020-08-25 22:25:02.637

I’ve listed the results using vertical output so that you don’t have to scroll sideways to see all columns.

In my case, there are two mail items in the queue, and so two rows are returned.

The last_empty_rowset_time column provides the date and time that the queue was last empty.

The last_activated_time column provides the date and time the queue was last activated.

Note that the sysmail_help_queue_sp stored procedure is in the msdb database and is owned by the dbo schema. Therefore, you’ll need to use three-part naming if msdb is not the current database.

Filter by Queue Type

As mentioned, there are two queue types (mail and status). You can also return items based on their queue type.

To do this, provide the @queue_type parameter and specify your desired queue type.

To return all items from the mail queue:

EXEC msdb.dbo.sysmail_help_queue_sp
    @queue_type = 'mail';

Result:

queue_type             | mail
length                 | 0
state                  | INACTIVE
last_empty_rowset_time | 2020-08-25 22:25:02.637
last_activated_time    | 2020-08-25 22:25:02.637

To return all items from the status queue:

EXEC msdb.dbo.sysmail_help_queue_sp
    @queue_type = 'status';

Result:

queue_type             | status
length                 | 0
state                  | INACTIVE
last_empty_rowset_time | 2020-08-25 22:25:02.637
last_activated_time    | 2020-08-25 22:25:02.637