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?


By alert I mean do we have something in AG as shown here as a message seen from db mirroring?

2 Answers 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).


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.

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;


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**

    --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
    -- ag.name, ar.replica_server_name, adc.database_name;
    drs.log_send_queue_size + drs.redo_queue_size DESC;


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

  • 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 Commented Oct 20, 2020 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
    Commented Oct 20, 2020 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 Commented Oct 20, 2020 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
    Commented Oct 21, 2020 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 Commented Oct 21, 2020 at 17:03

I would also suggest tracking your send queue as I've found with our systems having 5 secondaries (2 reporting async, 2 failover async, 1 failover sync) that some of the asyncs hold onto a huge send queue which I believe is happening from CPUs running parallel processing because maxDOP is too high so CPU wait times causing sends to be delayed on large (40GB~ log files).

send queue/send kbs = time to send remaining, figure out a good threshold you expect your data to be sent by and if you exceed that trigger those send queue alerts if it's going to be above that.

I would also suggest any large indexing jobs you limit your maxDOP on those to something less than your server/data DOP to ensure sync doesn't get held up from indexing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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