sqmSQLTool › Commands › Get-sqmServiceBrokerHealth

Get-sqmServiceBrokerHealth Always On

Generates a TXT health report for SQL Server Service Broker: endpoints, per-database broker status, queue configuration, undeliverable messages in the transmission queue, service/contract pairs, and (when AlwaysOn is detected) AG replica state.

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

#SectionSource
1Service Broker Endpointssys.endpoints WHERE type=3
2Broker Status per Databasesys.databases WHERE state_desc='ONLINE'
3Service Queuessys.service_queues (per broker-enabled DB)
4Undeliverable Messagessys.transmission_queue (per broker-enabled DB)
5Service Pairs and Contractssys.services + sys.service_contracts
6AlwaysOn Replica Statusdm_hadr_availability_replica_states (only if AG detected)

Syntax

Get-sqmServiceBrokerHealth
    [-SqlInstance <String>]          # default $env:COMPUTERNAME
    [-SqlCredential <PSCredential>]
    [-OutputPath <String>]           # default C:\System\WinSrvLog\MSSQL
    [-ContinueOnError]
    [-EnableException]
    [-NoOpen]

Parameters

ParameterTypeDefaultDescription
-SqlInstanceString$env:COMPUTERNAMESQL Server instance. Accepts pipeline input.
-SqlCredentialPSCredential—Optional SQL connection credential.
-OutputPathStringC:\System\WinSrvLog\MSSQLOutput directory for the TXT report.
-ContinueOnErrorSwitchfalseDo not rethrow on error.
-EnableExceptionSwitchfalseThrow terminating exceptions immediately.
-NoOpenSwitchfalseSuppress automatic opening of the report in Notepad.

Return Value

PropertyDescription
ComputerNameResolved server name from $server.Name.
ReportPathFull path to the written TXT report file.
TimestampReport generation timestamp (yyyyMMdd-HHmmss).
IsAlwaysOn$true when at least one availability group was detected.

Examples

# Local instance, default output path
Get-sqmServiceBrokerHealth

# Remote instance with custom output path
Get-sqmServiceBrokerHealth -SqlInstance "SQL01" -OutputPath "D:\Reports"

# Pipeline from OU discovery
Get-sqmServersFromOU | Get-sqmServiceBrokerHealth -ContinueOnError -NoOpen