performance: msdb.dbo.sp_readrequest;1 – long running process

When monitoring the new installed version of SQL 2008 R2 SP1, I have encountered that Database mail leaves a hung process few minutes. This doesn’t do any blockages or other trouble to your SQL installation, either that it is anoying to see over and over again this long running process. In order to get rid off it, just check the system parameter value for “DatabaseMailExeMinimumLifeTime” running the following query:

Use msdb
GO
exec sysmail_help_configure_sp 'DatabaseMailExeMinimumLifeTime'
GO

The default setting will be 600. You need to change it to a lower period.

Use msdb
GO
exec sysmail_configure_sp 'DatabaseMailExeMinimumLifeTime', 30
GO

=================================================================

Anyone get a solution to this one?  I'm getting the same hung process today after an upgrade to 2005 SP3

Had a look at the mail logs - the process runs for 10minutes.  Which is coincidentally the same as the setting for 'Database Mail Executable Minimum Lifetime (seconds)'

When I adjusted 'Database Mail Executable Minimum Lifetime (seconds)' down from the 10 minutes default to 10 seconds everything went back to normal.  Much happiness for me. 

I am troubleshooting a similar issue, whereby the Performance Dashboard Reports (the Query Duration Component) show that a stored procedure used by Database Mail is the most costly query. It appears to be a service broker wait request, i.e. the database mail queue is waiting for further requests to process.

Based on your comments this seem like a plausable solutiuon. I have adjusted the very same Database Mail property to see if it clears this issue.

Error: Cannot drop the database because it is being used for replication

Hi,

Try using sp_removedbreplication ‘dbname’

Find text in Stored Procedures View Trigger and Function and Troubleshoot Replication Ambiguous Column

Today I'm faced Challenge in replication when added all tables, Sps,VIEWs and user defined functions.

Error : Its return "Ambiguous Column"

Tried and found below query to identified the root cause of issue.


DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'foobar', @notcontain = 'comments'

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0
or CHARINDEX(@notcontain,syscomments.text)<>0))