SQL Server: Session has been running for a long time with the sp_readrequest (DatabaseMail) command

Hello people,
Good afternoon!

In this post, I will share with you a learning that I had this week. In the company where I work, the version of RedGate SQL Monitor was recently updated and with this, several alerts for instance monitoring were created.

One such monitoring is long running queries, which identifies sessions that have been executing an instruction for more than X minutes. We set this time to 3 minutes, so any session that executes a command that exceeds this runtime in production will be alerted by the analyzing DBA team.

The problem is that we identified that there was a session that was always alerted and ended up firing this alert several to several times every day, generating spam in our email and causing this alert to lose its importance.

Looking at the alert, we can see that this session was from Database Mail, a SQL Server utility itself, which ran the sp_readrequest command, as the alert shows us below:

The sp_readrequest stored procedure is a system procedure, which basically reads requests for messages from the queue and returns their content. This process is active in the instance for a minimum time, determined by the parameter “DatabaseMailExeMinimumLifeTime”, whose default time is 600 seconds. That is, whenever this process starts (and it starts constantly), it will be running for at least 600 seconds, and therefore, the alert was always triggered.

To resolve this issue so that I don't have to change or disable my alert, just change this default minimum time:

Using the SQL Server Management Studio Inferface
To change this parameter through SQL Server Management Studio, simply access the “Management”> “Database Mail” session

Now, check the option “View or change system parameters” and click “Next”

In this screen, it is possible to view the current value and define a new value for the “Database Mail Executable Minimium Lifetime (seconds)” parameter.

Using Transact-SQL
To view the default value of the “” parameter using Transact-SQL (TSQL) commands, just use the query below:

And if you want to change the parameter value, just UPDATE the msdb.dbo.sysmail_configuration table:

or execute the sysmail_configure_sp stored procedure:

An important point to keep in mind is that this parameter serves to limit the minimum time that the DatabaseMail process will execute for each connection.

With a very high minimum time, the process will be active on the instance even if it has no message to deliver.

If you set a very low minimum time, the process will be active on the instance for less time, but it will also generate more connections and disconnects on the instance to process this activity, causing a small overhead because of it. If your instance always has many, many messages in Database Mail to deliver, consider increasing this minimum time to decrease this connection overhead.

Hope you enjoyed the post and see you next time!
Hug.

SQL Server Session has been running for a long time with the sp_readrequest (DatabaseMail) command Why a Session With sp_readrequest Takes So Long to Execute Red Gate SQL Monitor alert long-running query

SQL Server Session has been running for a long time with the sp_readrequest (DatabaseMail) command Why a Session With sp_readrequest Takes So Long to Execute Red Gate SQL Monitor alert long-running query