Oldest Unsent Transaction Alert Event ID 32040

Last week, during my on-call stint I was paged for an error. This error message wasn’t unfamiliar actually but I have seen this error message after a long time. This message was kind of, informational message but at the same time I need to make sure that it don’t really get converted into an issue. The reported message was – Event ID 32040. This message is related to Database Mirroring, and some of our client uses Database Mirroring.

Event ID 32040 is reported at the Principal server instance, it send alerts when a transaction reaches to a threshold value for age of transaction which is valued in minutes. According to MS article

Typically, this event occurs because the performance of the system has changed. Either the bandwidth between the two systems has decreased, or the load has increased.

The age of the oldest unsent transaction is a performance metric that can help you evaluate the potential for data loss as measured by the number of minutes of unsent transactions. This metric is especially relevant for high-performance mode sessions. However, this metric is also relevant for high-safety mode session when mirroring is paused or suspended because the partners become disconnected.

In our case, the client has some deployment a day before it paged me. But in other case, it is really much an easy job to fix this. Basically, it will alert in cases below:

There isn’t any network /bandwidth issue
If there is a deployment or change happened for the said database

How would you fix Event ID 32040?

You will have to use the system stored procedure sp_dbmmonitorchangealert.

sp_dbmmonitorchangealert 
    database_name   
    , alert_id   
    , alert_threshold   
    , enabled

Since our case was of deployment it was nothing much I could do so I decided to wait and let the change transferred to the secondary. It took a while but finally, it was all good. I hope you like this blog post. In case you want to read some more tips and tricks I will encourage you to browse through HA&DR category.

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.