Commands / Get-sqmAlwaysOnQueueStatus
sqmSQLTool › Commands › Get-sqmAlwaysOnQueueStatus

Get-sqmAlwaysOnQueueStatus Always On

Returns the current redo queue / send queue status for all Always On availability groups on an instance - no files written. Lightweight counterpart to Get-sqmAlwaysOnHealthReport: same DMV query and threshold scoring, but the row objects come back directly instead of TXT/CSV/HTML reports. Built for callers that poll on a schedule, where writing report files on every run would just accumulate disk clutter.

Execution Flow

START dbatools available? throw ERROR foreach $instance in $SqlInstance [pipeline] Get-sqmAlwaysOnQueueSnapshot (private, shared helper) inside the shared helper Get-DbaAvailabilityGroup - no AG → return empty array (not an error) DMV query: ag/ar/ars/adbrs - role, sync state, redo/send queue, suspend Convert KB → MB, compare against MaxRedoQueueMB / MaxSendQueueMB OverallStatus: Critical / Warning / OK per DB/replica row No file I/O anywhere in this path Emit rows to the pipeline directly (no accumulation) DONE

Synopsis

Queries all AGs on the given SQL Server instance via dbatools and DMVs - the same query and threshold scoring as Get-sqmAlwaysOnHealthReport, factored into a shared private helper so the logic only exists once. For each AG, every replica/database row is evaluated: redo queue, send queue, synchronization state, connection state, and suspend status determine an OverallStatus (OK / Warning / Critical). Nothing is written to disk - rows are returned straight to the pipeline, one call per instance.

Requires dbatools and VIEW SERVER STATE on the target instance. An instance without any availability groups returns an empty array, not an error - that is the expected result on a non-AG instance, not a failure.

Why this exists alongside Get-sqmAlwaysOnHealthReport

Get-sqmAlwaysOnHealthReport writes a TXT, CSV, and interactive HTML report file to disk on every call - fine for an on-demand report, wrong for something invoked every few minutes by a central-poller (e.g. the SQLLiveDiagnose collector monitoring several instances). Get-sqmAlwaysOnQueueStatus is that same data with the file I/O removed: same parameters, same threshold logic, no -OutputPath/-OutputHtml, just the row objects.

Syntax

Get-sqmAlwaysOnQueueStatus
    [-SqlInstance <String[]>]    # pipeline
    [-SqlCredential <PSCredential>]
    [-MaxRedoQueueMB <Int>]
    [-MaxSendQueueMB <Int>]
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString[]$env:COMPUTERNAMESQL Server instance(s) to query. Pipeline-capable.
-SqlCredentialPSCredential—Credential for the SQL connection.
-MaxRedoQueueMBInt100Redo queue warning threshold in MB (secondary replicas).
-MaxSendQueueMBInt50Send queue warning threshold in MB.
-EnableExceptionSwitchfalseThrow terminating exceptions instead of Write-Error.

OverallStatus Logic

StatusCondition
CriticalDbSyncState not in (SYNCHRONIZED/SYNCHRONIZING) OR ConnectionState ≠ CONNECTED OR IsSuspended = true.
WarningRedoQueueMB > MaxRedoQueueMB (secondary) OR SendQueueMB > MaxSendQueueMB.
OKAll checks passed.

Examples

Example 1, Queue status for the local instance

Get-sqmAlwaysOnQueueStatus

Example 2, Only rows that need attention

Get-sqmAlwaysOnQueueStatus -SqlInstance "SQL01" -MaxRedoQueueMB 200 | Where-Object OverallStatus -ne 'OK'

Example 3, Poll several instances and push into a repository table

"SQL01","SQL02" | Get-sqmAlwaysOnQueueStatus | Write-DbaDbTableData -SqlInstance "Repo01" -Database "SQLLiveDiagnose" -Table "AlwaysOnQueueSnapshot"
⬆ Index
All Commands
📄 Related
Get-sqmAlwaysOnHealthReport