This error occurs when Availability replica changes its role.
This error occurs when Availability replica changes its role.
This event is disabled in SQL Server by default. It can be enabled by means of the following TSQL: sp_altermessage 19406, 'with_log', 'true'
The replica state changed because of either a startup, a failover, a communication issue, or a cluster error. See the event for additional information.
If the “changed to” state is PRIMARY_PENDING, then check sys.dm_hadr_database_replica_states. If database_state_desc = RECOVERY_PENDING (synchronization_health_desc will be NOT_HEALTHY), then try “ALTER DATABASE db SET HADR RESUME;”. Else if this is the only replica (no secondary replica), then (consider first taking a database snapshot as a backup if required) try “ALTER DATABASE db SET HADR OFF;” to remove Always On so as to then manually recover database with SQL service or database restart (ALTER DATABASE ONLINE).
TSQL: ALTER DATABASE DbName SET HADR RESUME;
TSQL: ALTER DATABASE DbName SET ONLINE;
TSQL: RESTORE DATABASE DbName WITH RECOVERY;
If the “changed to” state is RESOLVING_NORMAL, check for additional messages.
If “change to” state is PRIMARY_NORMAL / SECONDARY_NORMAL, then this may indicate a successful failover. Check for additional messages if failover was not expected.
Name | Description | Default Value |
Enabled | Enables or disables the workflow. | Yes |
Priority | Defines Alert Priority. | 1 |
Severity | Defines Alert Severity. | 1 |
Target | Microsoft.SQLServer.2012.AlwaysOn.AvailabilityReplica | ||
Category | EventCollection | ||
Enabled | True | ||
Alert Generate | True | ||
Alert Severity | Warning | ||
Alert Priority | Normal | ||
Remotable | True | ||
Alert Message |
| ||
Event Log | Application |
ID | Module Type | TypeId | RunAs |
---|---|---|---|
DS | DataSource | Microsoft.Windows.EventProvider | Default |
GenerateAlert | WriteAction | System.Health.GenerateAlert | Default |
<Rule ID="Microsoft.SQLServer.2012.AlwaysOn.AvailabilityReplicaRoleChanged" Target="AlwaysOnDisc!Microsoft.SQLServer.2012.AlwaysOn.AvailabilityReplica" Enabled="true" ConfirmDelivery="true" Remotable="true">
<Category>EventCollection</Category>
<DataSources>
<DataSource ID="DS" TypeID="Windows!Microsoft.Windows.EventProvider">
<ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
<LogName>Application</LogName>
<Expression>
<And>
<Expression>
<Or>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">PublisherName</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2012.AlwaysOn.AvailabilityReplica"]/PerformanceObjectName$</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">PublisherName</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<!--Default instance with SQLServer object name-->
<Value Type="String">MS$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2012.AlwaysOn.AvailabilityReplica"]/PerformanceObjectName$</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</Or>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="UnsignedInteger">EventDisplayNumber</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="UnsignedInteger">19406</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
<Expression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Params/Param[1]</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">$Target/Property[Type="AlwaysOnDisc!Microsoft.SQLServer.2012.AlwaysOn.AvailabilityReplica"]/AvailabilityGroupName$</Value>
</ValueExpression>
</SimpleExpression>
</Expression>
</And>
</Expression>
</DataSource>
</DataSources>
<WriteActions>
<WriteAction ID="GenerateAlert" TypeID="Health!System.Health.GenerateAlert">
<Priority>1</Priority>
<Severity>1</Severity>
<AlertMessageId>$MPElement[Name="Microsoft.SQLServer.2012.AlwaysOn.AvailabilityReplicaRoleChanged.AlertMessage"]$</AlertMessageId>
<AlertParameters>
<AlertParameter1>Event ID: $Data/EventDisplayNumber$. $Data/EventDescription$</AlertParameter1>
</AlertParameters>
<Suppression>
<SuppressionValue/>
</Suppression>
</WriteAction>
</WriteActions>
</Rule>