Execution Flow
START
dbatools
available?
throw ERROR
Connect-DbaInstance → $server; mkdir $OutputPath if needed
SELECT COUNT(*) FROM sys.availability_groups → $isAlwaysOn
Report sections — appended to $reportContent list
① Endpoints: sys.endpoints WHERE type=3 (SERVICE_BROKER) → name, state_desc, protocol_desc
② Broker status per DB: sys.databases WHERE state_desc='ONLINE' → is_broker_enabled; count enabled/disabled
③ Queues (per broker-enabled DB): sys.service_queues → is_enqueue_enabled, is_receive_enabled,
is_activation_enabled, is_retention_enabled
④ Undeliverable msgs (per broker-enabled DB): SELECT COUNT(*) FROM sys.transmission_queue
⑤ Services & contracts: sys.services LEFT JOIN sys.service_contract_usages JOIN sys.service_contracts
⑥ (if $isAlwaysOn) AlwaysOn replica state: sys.availability_groups JOIN sys.availability_replicas
JOIN sys.dm_hadr_availability_replica_states → role_desc, operational_state_desc
Write report: $reportContent -join "`n" | Out-File $reportFile -Encoding UTF8
if (-not $NoOpen): notepad.exe $reportFile
throw
Return [PSCustomObject] { ComputerName, ReportPath, Timestamp, IsAlwaysOn }
DONE
Synopsis
Connects via dbatools and runs six report sections against the target instance. Sections 3–5 iterate only over broker-enabled ONLINE databases. AlwaysOn is auto-detected at startup — when sys.availability_groups returns any row, section 6 is included. The full report is a single UTF-8 TXT file; if -NoOpen is not set, Notepad opens automatically.
Requires dbatools. Default output path: C:\System\WinSrvLog\MSSQL. Report file name: ServiceBrokerHealth_<server>_yyyyMMdd-HHmmss.txt. SupportsShouldProcess with ConfirmImpact=None (file write).
Report Sections
# Section Source 1 Service Broker Endpoints sys.endpoints WHERE type=32 Broker Status per Database sys.databases WHERE state_desc='ONLINE'3 Service Queues sys.service_queues (per broker-enabled DB)4 Undeliverable Messages sys.transmission_queue (per broker-enabled DB)5 Service Pairs and Contracts sys.services + sys.service_contracts6 AlwaysOn Replica Status dm_hadr_availability_replica_states (only if AG detected)
Syntax
Get-sqmServiceBrokerHealth
[-SqlInstance <String>]
[-SqlCredential <PSCredential>]
[-OutputPath <String>]
[-ContinueOnError ]
[-EnableException ]
[-NoOpen ]
Parameters
Parameter Type Default Description -SqlInstance String $env:COMPUTERNAME SQL Server instance. Accepts pipeline input. -SqlCredential PSCredential — Optional SQL connection credential. -OutputPath String C:\System\WinSrvLog\MSSQL Output directory for the TXT report. -ContinueOnError Switch false Do not rethrow on error. -EnableException Switch false Throw terminating exceptions immediately. -NoOpen Switch false Suppress automatic opening of the report in Notepad.
Return Value
Property Description ComputerName Resolved server name from $server.Name. ReportPath Full path to the written TXT report file. Timestamp Report generation timestamp (yyyyMMdd-HHmmss). IsAlwaysOn $true when at least one availability group was detected.
Examples
Example 1 — Local instance, default output path
Get-sqmServiceBrokerHealth copy
Example 2 — Remote instance with custom output path
Get-sqmServiceBrokerHealth -SqlInstance "SQL01" -OutputPath "D:\Reports" copy
Example 3 — Pipeline from OU discovery
Get-sqmServersFromOU | Get-sqmServiceBrokerHealth -ContinueOnError -NoOpen copy