x

msdb database mail taking longer than 5 minutes

Hi Guys,

I am using SQL server 2008R2 instance which is monitored by red-gate SQL monitor. Database mail process is taking more than 5 minutes to execute. It always come up in my long running query list.

Below is the sql fragment. I have no idea how to make it execute faster and where to tune it. Any direction will be much appreciated.

Process ID: 79
Process name:   DatabaseMail - DatabaseMail - Id<8708>
Database:   msdb
Host:   SQL2K8R2
User:   MyUser
Process login time: 5 Oct 2011 5:03 PM
Query start time:   5 Oct 2011 5:03 PM
Query duration: 585 sec
SQL process fragment
-- sp_readrequest : Reads a request from the the queue and returns its
-- contents.
CREATE PROCEDURE sp_readrequest
@receive_timeout INT -- the max time this read will wait for new message
AS
BEGIN
SET NOCOUNT ON

-- Table to store message information.
DECLARE @msgs TABLE
(
[conversation_handle] uniqueidentifier,
[service_contract_name] nvarchar(256),
[message_type_name] nvarchar(256),
[message_body] varbinary(max)
)

-- Declare variables to store row values fetched from the cursor
DECLARE 
@exit INT,
@idoc INT,
more ▼

asked Oct 04, 2011 at 10:03 PM in Default

NaveenK gravatar image

NaveenK
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

@NaveenK - Have you checked how many entries are in the msdb.dbo.sysmail_mailitems table? I suspect you have a lot of entries in there which could be slowing down the process of reading/processing them.

You can use the stored procedure sysmail_delete_mailitems_sp to clean up old email items that are filling this table.

You run it like this:

EXEC sysmail_delete_mailitems_sp 
@sent_before = '2011-01-01' , -- enter a date that suits you.
@sent_status = 'sent' -- possible values = sent, unsent, retrying, failed
more ▼

answered Oct 05, 2011 at 01:04 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

Hi William, Thanks for the quick response there are 8797 entries in msdb.dbo.sysmail_mailitems. I will clean the old entries. But 9 K entries are not much.
Oct 05, 2011 at 01:02 PM NaveenK
"But 9K entries are not much" - not normally, no, but it depends on how those entries are being processed.
Oct 05, 2011 at 01:52 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

Check this out: http://www.red-gate.com/messageboard/viewtopic.php?t=8839

I believe it's functioning as expected. It looks to me like it's the database mail message queue handler. I did: SELECT count(*) FROM [msdb].[dbo].[sysmail_mailitems]

And i had 11360 items. When I looked at the storage size, it was about 5MB. Not that large considering it's a 1TB data warehouse.

I would say ignore.
more ▼

answered Jun 05, 2013 at 01:20 PM

nickmorse gravatar image

nickmorse
0 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x579
x18

asked: Oct 04, 2011 at 10:03 PM

Seen: 3298 times

Last Updated: Jun 05, 2013 at 01:20 PM