sqmSQLTool › Commands › Get-sqmOperationStatus

Get-sqmOperationStatus Always OnPerformance

Monitors running SQL Server long-operations in real time: Backup, Restore (percent_complete from dm_exec_requests) and AlwaysOn AutoSeed operations (dm_hadr_physical_seeding_stats). Supports continuous refresh mode for watching active operations. Console output only — no file export.

Execution Flow

START dbatools available? throw ERROR -Continuous switch? do { ... } while ($Continuous) — refreshes every $RefreshSeconds if OperationType ∈ (Backup, Restore) or All dm_exec_requests WHERE command IN ('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE','RESTORE LOG') AND percent_complete > 0 if OperationType ∈ (AutoSeed) or All dm_hadr_physical_seeding_stats WHERE internal_state_desc IN ('RUNNING','IN_PROGRESS') Graceful catch: "Invalid object name" → pre-SQL2016 (DMV not available) Format-sqmTimeSpan → elapsed/remaining; Format-sqmFileSize → bytes Clear-Host + Write-Host formatted table if -Continuous Start-Sleep -Seconds $RefreshSeconds (if -Continuous) Return $allOps[] via pipeline DONE

Synopsis

Two separate DMV queries collect active long-operations: dm_exec_requests for Backup/Restore (filtered by percent_complete > 0) and dm_hadr_physical_seeding_stats for AutoSeed (SQL 2016+; gracefully skipped on older versions). Output is formatted for the console using Format-sqmTimeSpan and Format-sqmFileSize. There is no file export — results are returned via the pipeline.

Requires dbatools. AutoSeed monitoring requires SQL Server 2016+. With -Continuous, the function loops until interrupted (Ctrl+C), clearing the console on each refresh. No -OutputPath parameter; this function is console-only.

Syntax

Get-sqmOperationStatus
    [-SqlInstance <String>]
    [-SqlCredential <PSCredential>]
    [-OperationType <String>]       # Backup | Restore | AutoSeed | All (default: All)
    [-Continuous]
    [-RefreshSeconds <Int>]          # default 10
    [-EnableException]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMESQL Server instance to monitor.
-SqlCredentialPSCredential—SQL connection credential.
-OperationTypeStringAllFilter to Backup, Restore, AutoSeed, or All.
-ContinuousSwitchfalseLoop with Clear-Host; press Ctrl+C to stop.
-RefreshSecondsInt10Seconds between refreshes in Continuous mode.
-EnableExceptionSwitchfalseThrow terminating exceptions.

Return Value

PropertyDescription
OperationTypeBackup, Restore, or AutoSeed.
DatabaseNameTarget database name.
PercentCompleteProgress percentage (Backup/Restore). AutoSeed uses TransferRate instead.
ElapsedTimeFormatted elapsed time (Format-sqmTimeSpan).
EstimatedTimeRemainingFormatted estimated time remaining.
TransferRateAutoSeed: transfer_rate_bytes_per_second (formatted).
TransferredSizeAutoSeed: transferred_size_bytes (formatted).
RoleAutoSeed: role_desc (PRIMARY / SECONDARY).

Examples

# One-shot: show all active operations
Get-sqmOperationStatus -SqlInstance "SQL01"

# Watch backup progress, refresh every 5 seconds
Get-sqmOperationStatus -SqlInstance "SQL01" -OperationType "Backup" -Continuous -RefreshSeconds 5

# AutoSeed only (Always On Seeding)
Get-sqmOperationStatus -OperationType "AutoSeed" -Continuous