2

I am trying to figure out the way to monitor these 2 events

  1. log send queue size - I can see this in perfmon

  2. 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?

2

Both the log send queue and redo queue can be found in sys.sys.dm_hadr_database_replica_states (docs), and also available using the SQLServer: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:

Amount of log records in the log files of the primary database, in kilobytes, that haven't been sent to the secondary replica. This value is sent to the secondary replica from the primary replica. Queue size doesn't include FILESTREAM files that are sent to a secondary.

The perfmon counter for the Redo Queue is actually the Recovery Queue counter, which is defined as:

Amount of log records in the log files of the secondary replica that have not been redone.

You can also access the Perfmon counters using T-SQL by querying the sys.dm_os_performance_counters DMV.

SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Database Replica'
AND counter_name IN ('Log Send Queue','Recovery Queue')
ORDER BY cntr_value DESC;

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.

-- ▶ Run this on **PRIMARY**
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT 
    --AG info
    AGName                          = ag.name, 
    ReplicaServer                   = ar.replica_server_name, 
    DbName                          = adc.database_name, 
    -- Queue Size & rates
    SendQueueSize                   = drs.log_send_queue_size,
    RedoQueueSize                   = drs.redo_queue_size, 
    SendRateKb                      = drs.log_send_rate, 
    RedoRateKb                      = drs.redo_rate, 
    -- State Info
    IsLocal                         = drs.is_local, 
    IsPrimary                       = drs.is_primary_replica, 
    SynchronizationState            = drs.synchronization_state_desc, 
    SynchronizationHealthState      = drs.synchronization_health_desc, 
    -- Last ____ Time
    LastSentTime                    = drs.last_sent_time, 
    LastRedoTime                    = drs.last_redone_time, 
    LastReceivedTime                = drs.last_received_time, 
    LastHardenedTime                = drs.last_hardened_time, 
    LastCommitTime                  = drs.last_commit_time, 
    -- LSN info
    RecoveryLSN                     = drs.recovery_lsn, 
    TruncationLSN                   = drs.truncation_lsn, 
    LastSentLSN                     = drs.last_sent_lsn, 
    LastReceivedLSN                 = drs.last_received_lsn, 
    LastHardenedLSN                 = drs.last_hardened_lsn, 
    LastRedoneLSN                   = drs.last_redone_lsn, 
    EndOfLogLSN                     = drs.end_of_log_lsn, 
    LastCommitLSN                   = drs.last_commit_lsn,
    --Oh yeah, filestream, too
    FileStreamSendRate              = drs.filestream_send_rate
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_databases_cluster AS adc 
    ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id
JOIN sys.availability_groups AS ag
    ON ag.group_id = drs.group_id
JOIN sys.availability_replicas AS ar 
    ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id
ORDER BY 
    -- ag.name, ar.replica_server_name, adc.database_name;
    drs.log_send_queue_size + drs.redo_queue_size DESC;

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 NewAlert-Menu

On the General tab of the New Alert window, select the Perfmon counter you want to monitor, as well as the alert threshold. NewAlert-General

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). NewAlert-Response

| improve this answer | |
  • Thanks Andy, much appreciated for nice explanation. Also are you aware how these events can be written to windows event log so that monitoring from some tools can pick this up to alert as needed – BeginnerDBA Oct 20 at 1:52
  • These aren't really events. Send/Redo queue are a measurement, like your car's speed. It sounds like you're looking to define a threshold so that if the queue becomes "too large" then exceeding that threshold becomes an event. But you'd need to define that threshold & create the alerting mechanism (the same way that exceeding the speed limit in your car creates an event where you get a ticket) – AMtwo Oct 20 at 3:12
  • Yes that is correct. I tried but could not find a way we can write an event after surpassing a defined threshold between 2 in event log for alerting, thanks – BeginnerDBA Oct 20 at 6:14
  • @BeginnerDBA I've added a section on using the SQL Agent alerting mechanism to trigger custom alerts based on the Perfmon counters. – AMtwo Oct 21 at 3:17
  • thanks ! That completely answers my question. One quick ask, when i run your script on primary , as suggested, all the metrics i am getting is from secondary like send queue size, redo queue size, send rate , redo rate, , i am seeing something wrong or that is expected and why no metrics from primary – BeginnerDBA Oct 21 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.