Friday, September 28, 2012

SQL Server: Why a Session With sp_readrequest Takes so Long to Execute



While applying, Long Running Sessions Detection Job on a production server, we start receiving alert that a session is taking more then 3 minutes. But what actually this session was doing. Here is the alert report.

SP ID
Stored Procedure Call
DB Name
Executing Since
58
msdb.dbo.sp_readrequest;1�
msdb
3 min
sp_readrequest is a system stored procedure, which basically reads a message request from the the queue and returns its  contents.
This process can remain active for a time we have configured for parameter DatabaseMailExeMinimumLifeTime, at the time of database mail profile configuration. 600 seconds is the default value for this external mail process. According to BOL DatabaseMailExeMinimumLifeTime is the The minimum amount of time, in seconds, that the external mail process remains active.
This can be changed, at the time of mail profile configuration or you can just use update query to change this time.

UPDATE msdb.dbo.sysmail_configuration
SET paramvalue = 60 --60 Seconds
WHERE paramname = 'DatabaseMailExeMinimumLifeTime'
We have changed this to 60 seconds to resolve our problem.

1 comment:

  1. -- Nice

    -- Also we can use

    Use msdb
    GO
    exec sysmail_configure_sp 'DatabaseMailExeMinimumLifeTime', 30
    GO

    ReplyDelete

All suggestions are welcome