I am trying to figure out the way to monitor these 2 events
-
log send queue size – I can see this in perfmon
-
Redo queue size – I can see in dmv but not in perfmon counter
Is there any way of using perfmon so that I can calculate the redo queue size even thought the counter is not available in perfmon?
Also, I see these events are logged in the Windows event viewer when they are part of database mirroring. But now using AG, how is it possible to log these 2 values in Windows event viewer for a certain range exceeding?
Edit
By alert I mean do we have something in AG as shown here as a message seen from db mirroring?
Best Answer
Both the log send queue and redo queue can be found in
sys.sys.dm_hadr_database_replica_states
(docs), and also available using theSQLServer:Database Replica
Perfmon counters (docs).Perfmon
With Perfmon counters, you'll need to collect counters individually on each replica. Each replica knows about the Send/Redo(recovery) queue affecting that replica itself.
It sounds like you already found the
Log Send Queue
perfmon counter, which is defined as:The perfmon counter for the Redo Queue is actually the
Recovery Queue
counter, which is defined as:You can also access the Perfmon counters using T-SQL by querying the
sys.dm_os_performance_counters
DMV.T-SQL / DMVs
From T-SQL, you can query the DMVs to get this info. The below query is from my standard toolbox, and includes both the Send & Redo queue sizes & rates (in the first few columns), plus some other information on the replica states & send/redo status. Note that running this on the Primary Replica will report status information for all replicas, running it on the secondary replica will only report local information for that secondary replica.
The advantage of monitoring via T-SQL is that you can have a single/central collection point to check on the status of all replicas at once.
Alerting
If you don't have monitoring product capable of watching the Perfmon Counters or DMVs directly, you can hack your way into alerting using the SQL Server Agent Alerting system.
In Object Explorer --> SQL Server Agent --> Alerts, select to create a new alert
On the General tab of the New Alert window, select the Perfmon counter you want to monitor, as well as the alert threshold.
On the Response tab, you can select to either send an email to notify SQL Agent Operators or execute a SQL Agent Job. You can have the SQL Agent job response run T-SQL or PowerShell that executes just about anything (like logging to the Windows Event Log).