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

avatar 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

avatar image

WilliamD
26.2k 18 34 48

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

avatar image

nickmorse
0 1 1 2

(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.

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:

x738
x16

asked: Oct 04, 2011 at 10:03 PM

Seen: 5742 times

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

Copyright 2016 Redgate Software. Privacy Policy