SQL Server: Mirror monitoring oldest unsent transaction alarm self-healing

This article describes how a oldest unsent Transaction threshold alarm in SQL Server mirroring monitoring can heal itself

image monitoring data and alarms are recorded and triggered by the database Mirroring Monitor job job, and when oldest unsent transaction exceeds threshold, the trigger code is 32040 error message.

The presence of alarms may be related to the load in the principal server instance and the mirror server instance and their network connectivity (most of the bloggers experience in maintenance), but after the load or network connection returns to normal, the oldest unsent transaction alarm information continues to appear, The alarm disappears until backup database or backup log.

For more information on this issue, see the links below

Spurious oldest unsent Transaction alerts for database mirroring

The alarm information is as follows from the instance log:

The alert for ' oldest unsent transaction ' have been raised. The current value of ' surpasses ', the threshold ' 30 '.

If there is only one database configuration image, then it is easy to locate the database with the alarm, if it is multiple database configuration image, from the alarm information, we do not know exactly which database alarm.

So, to achieve self-healing difficulties:

1. What is the alarm data?

2. How to calculate oldest unsent transaction

By analyzing the database mirroring Monitor Job, we can know that the oldest unsent transaction alarm appears due to sys.database_mirroring There is a large difference between LSN and MIRRORING_FAILOVER_LSN, and the result of oldest unsent transaction calculation in database Mirroring Monitor job job is more than threshold. Therefore the alarm is triggered.

Let's summarize this first:

(1) The alarm is due to the difference between the END_OF_LOG_LSN and mirroring_failover_lsn of the sys.database_mirroring table.

(2) Database Mirroring Monitor Job job can analyze and know which db alarm

(3) Backup database, backup LOG alarm will disappear

There are 2 ways to get the database name when alerting:

1. Overwrite the stored procedures associated with the database mirroring Monitor job Job, custom error codes and information

reference to SQL Sentry (SQL Sentinel) Technical support Steve Wright the blog, there is a detailed rewrite code

2. Extract the relevant code from the database mirroring Monitor job job to overwrite it, and customize the code snippet to get the data

(1) Building a table

650) this.width=650; "Src=" Https://s1.51cto.com/oss/201711/06/fbe054927cefe561a1b2b0d4d5e5fbef.png-wh_500x0-wm_3 -wmp_4-s_1706917713.png "title=" 51_1.png "alt=" Fbe054927cefe561a1b2b0d4d5e5fbef.png-wh_ "/>

(2) Collect data

650) this.width=650; "Src=" Https://s2.51cto.com/oss/201711/06/6688deda0f86c75cdeb5b6e62f0c8c41.png-wh_500x0-wm_3 -wmp_4-s_17753087.png "title=" 51_2.png "alt=" 6688deda0f86c75cdeb5b6e62f0c8c41.png-wh_ "/>

Once we have access to the alarm data, we can begin to develop the relevant "self-healing" solution.

Prerequisites (can be added according to the actual situation):

1. The body and the mirror are connected properly

Select

Database_id,mirroring_role,mirroring_role_desc,

Mirroring_state,mirroring_state_desc,

Mirroring_partner_instance,mirroring_partner_name

From sys.database_mirroring

where [email protected]_id

2. The subject is running without a long transaction

--Query execution time longer than Dbm_monitor_alerts table Time_behind threshold transaction

DECLARE @database_id int

DECLARE @time_behind int

Set @database_id =5

Select @time_behind =time_behind from msdb: dbm_monitor_alerts WHERE [email protected]_id

Select COUNT (1) as Long_transactions_count from Sys.dm_tran_database_transactions dt,sys.dm_tran_active_transactions St

where dt.transaction_id=st.transaction_id

and [Email protected]_id

and DATEDIFF (minute, St.transaction_begin_time, GETDATE ()) > @time_behind

3. No backup is running

SELECT *

From sys.dm_exec_requests as ER

WHERE [Email protected]_id

and Er.command in (' Backup LOG ', ' Backup DATABASE ')

Write oldest unsent transaction alarm self-healing script according to prerequisites

650) this.width=650; "Src=" Https://s1.51cto.com/oss/201711/06/0af971c0835233c8355651f453b9d867.png-wh_500x0-wm_3 -wmp_4-s_2936849611.png "title=" 51_3.png "alt=" 0af971c0835233c8355651f453b9d867.png-wh_ "/>

Deployment method:

1. Create SQL Agent Alerts monitoring error code: 32040, set the Execute job as the mirror alarm processing job in the response option

2. In the mirror alarm processing job, the 1th step is to collect the data, the 2nd step is the alarm self-healing

At this point, oldest unsent transaction alarm self-healing has been realized, according to the actual monitoring situation to optimize the relevant details

This article from "Aimax" blog, reproduced please contact the author!

SQL Server: Mirror monitoring oldest unsent transaction alarm self-healing