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
, enabledSince 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.
