SQL Server Alwayson Errors (Errors in SQL Server)

SQL Server Alwayson Errors (Errors in SQL Server)

Contents

  • SQL Server AlwaysOn errors (errors in SQL Server)
  • SQL Server AlwaysOn (errors in SQL Server Configuration Manager)
  • SQL Server AlwaysOn (New Availability Group wizard errors)
  • SQL Server AlwaysOn (SSMS errors)
  • SQL Server AlwaysOn (performance issues)
  • SQL Server AlwaysOn errors (network)
  • SQL Server AlwaysOn errors (Application issues)
  • SQL Server AlwaysOn errors (cluster log)
  • SQL Server AlwaysOn errors (Windows messages obtained through “net helpmsg”)
  • SQL Server AlwaysOn errors (WMI errors)
  • SQL Server AlwaysOn (patching/updates in SQL 2012)

Tips to search this document: Try searching on error number, or on part of error message, or on performance issue like “hang”, wait type like “HADR_SYNC_COMMIT”, or on database state like “RECOVERY_PENDING” (without quotes).

Ideally this information is available through below logs:

  • Executing TSQL etc. commands through SQL Management Studio.
  • SQL ERRORLOG files
  • SQL default traces in LOG folder
  • Event logs
  • Cluster logs.

SQL Server AlwaysOnerrors (errors in SQL Server)

error / severity / description
957 / 17 / Database '%.*ls' is enabled for database mirroring or has joined an availability group. The name of the database cannot be changed.
976 / 14 / The target database, '%.*ls', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other /
  • If dm_hadr_availability_replica_states.role_desc=3(INVALID), then check service broker. Because Availability group is using Service Broker to communicate between SQL Server then need to check Service Broker first. Open SSMS>“Server Objects” > “Endpoints”, there are two “Service Broker” folder>Expand both, and if the state is Stopped or Disabled, then can change the state to Started.
  • Similar steps as in error 35250.

978 / 14 / The target database ('%.*ls') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.
979 / 14 / The target database ('%.*ls') is in an availability group and currently does not allow read only connections. For more information about application intent, see SQL Server Books Online.
982 / 14 / Unable to access the '%.*ls' database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled r
983 / 14 / Unable to access database '%.*ls' because its replica role is RESOLVING which does not allow connections. Try the operation again later. /
  • Check SQL ERRORLOGs, event logs, etc. for network, storage related messages etc.

1408 / 16 / The remote copy of database "%.*ls" is not recovered far enough to enable database mirroring or to join it to the availability group. You need to apply missing log records to the remote database by restoring the current log backups from the principal/prim
1409 / 16 / Database "%.*ls" requires database logs to be restored either on the future mirror database before you can enable database mirroring or on a secondary availability database before you can join it to the availability group. Restore current log backups from
1465 / 16 / Database "%.*ls" database is not in full recovery mode on each of the server instances. The full recovery model is required for a database to participate in database mirroring or in an availability group.
1466 / 16 / Database "%.*ls" is read-only on one of the server instances which is incompatible with participating in database mirroring or in an availability group. Set the database to read-write mode, and retry the operation.
1467 / 16 / Database "%.*ls" database is in emergency or suspect mode on one of the partners which is incompatible with participating in database mirroring or in an availability group.
1468 / 16 / The operation cannot be performed on database "%.*ls" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availabilit
1469 / 16 / Database "%.*ls" is an auto-close database on one of the partnerswhich is incompatible with participating in database mirroring or in an availability group.
1480 / 10 / The %S_MSG database "%.*ls" is changing roles from "%ls" to "%ls" because the mirroring session or availability group failed over due to %S_MSG. This is an informational message only. No user action is required. /
  • This is an informational message only. If there is a problem, then look for additional messages.

1488 / 16 / Database "%.*ls" database is in single user mode which is incompatible with participating in database mirroring or in an availability group. Set database to multi-user mode, and retry the operation.
1833 / 16 / File '%ls' cannot be reused until after the next BACKUP LOG operation. If the database is participating in an availability group, a dropped file can be reused only after the truncation LSN of the primary availability replica has passed the drop LSN of the
3104 / 16 / RESTORE cannot operate on database '%ls' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability gro
3402 / 10 / The database '%ls' is marked %ls and is in a state that does not allow recovery to be run. /
  • If the second %ls is RESTORING, then one option is below.
1) Asked the customer to stop the SQL Server Service.
2) Had the customer rename the actual files on disk with a "_old" suffix so that the sql server would not find them and have the db come up suspect.
3) Asked the customer to start the SQL Server Service.
Once the server came back up we were able to delete the database.
4) Had the customer create a new database with the same name and location as the original database. Used the output from sysaltfiles to verify this.
5) Had the customer stop the SQL Server Service.
6) Had the customer rename these new database files by appending a "_new" on the end of the file.
7) Had the customer take the old database files with the "_old" suffix and changed them back to the original name.
8)Had the customer start the SQL Server Service.
3633 / 16 / The operating system returned the error '%ls' while attempting '%ls' on '%ls' at '%hs'(%d). /
  • Verify error in %ls at DOS prompt with “net helpmsg”. Then proceed depending on this error.

3752 / 16 / The database '%.*ls' is currently joined to an availability group. Before you can drop the database, you need to remove it from the availatility group. /
  • Before you can drop the database, you need to remove it from the availability group. If AG exists, try ALTER AVAILABILITY GROUP [AGname] REMOVE DATABASE [DbName]. If AG does not exist, try removing with ALTER DATABASE [DbName] SET HADR OFF.
  • If replica_id and group_database_id in sys.databases show as null, then this may be due to earlier meta-data when database was part of an AG so stop SQL Server, rename the database files and start SQL Server. Then you should be able to remove the database.

5529 / 16 / Failed to remove a FILESTREAM file. The database is a primary database in an availability group. Wait for the FILESTREAM data files to be hardened on every secondary availability replica. Then retry the drop file operation.
10786 / 16 / The ALTER AVAILABILITY GROUP command failed because it contained multiple MODIFY REPLICA options: %ls. Enter a separate ALTER AVAILABILITY GROUP ... MODIFY REPLICA command for each replica option that you want to modify.
13251 / 10 / availability group
19401 / 16 / The READ_ONLY_ROUTING_URL '%.*ls' specified for availability replica '%.*ls' is not valid. It does not follow the required format of 'TCP://system-address:port'. For information about the correct routing URL format, see the CREATE AVAILABILITY GROUP docum
19403 / 16 / The availability replica '%.*ls' specified in the READ_ONLY_ROUTING_LIST for availability replica '%.*ls' does not exist. Only availability replicas that belong to the specified availability group '%.*ls' can be added to this list. To get the names of ava
19405 / 16 / Failed to create, join or add replica to availability group '%.*ls', because node '%.*ls' is a possible owner for both replica '%.*ls' and '%.*ls'. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try ag /
  • May occur if AG involves two or more SQL failover instances. Check possible owners of SQL resource on both/more so as to verify no node name overlap. There should not be node-overlap.
TSQL: SELECT * FROM sys.dm_os_cluster_nodes;
Powershell: get-clusterownernode -resource "XXXBSQL"
Powershell: set -clusterownernode -resource "XXXASQL" -owners NODE1,NODE2
19406 / 10 / The state of the local availability replica in availability group '%.*ls' has changed from '%ls' to '%ls'. The replica state changed because of either a startup, a failover, a communication issue, or a cluster error. For more information, see the availabi /
  • If the changed 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 AlwaysOn 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 state is RESOLVING_NORMAL, check for additional messages.

19407 / 16 / The lease between availability group '%.*ls' and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is fail
19452 / 16 / The availability group listener (network name) with Windows Server Failover Clustering resource ID '%s', DNS name '%s', port %hu failed to start with a permanent error: %u. Verify port numbers, DNS names and other related network configuration, then retry
19453 / 16 / The availability group listener (network name) with Windows Server Failover Clustering resource ID '%s', DNS name '%s', port %hu failed to start with this error: %u. Verify network and cluster configuration and logs.
19454 / 16 / The availability group listener (network name) with Windows Server Failover Clustering resource ID '%s', DNS name '%s', port %hu failed to stop with this error: %u. Verify network and cluster configuration and logs.
19455 / 16 / The WSFC cluster does not have a public cluster network with an IPv4 subnet. This is a requirement to create an availability group DHCP listener. Configure a public network for the cluster with an IPv4 subnet, and try to create the listener.
19456 / 16 / None of the IP addresses configured for the availability group listener can be hosted by the server '%.*ls'. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address which can b /
  • Added a new IP address (with different subnet) manually to the existing Listener [to be tested].

19458 / 16 / The WSFC nodes that host the primary and secondary replicas belong to different subnets. DHCP across multiple subnets is not supported for availability replicas. Use the static IP option to configure the availability group listener.
19460 / 16 / The availability group listener with DNS name '%.*ls' is configured to use DHCP. For listeners with this configuration, IP addresses cannot be added through SQL Server. To add IP addresses to the listener, drop the DHCP listener and create it again config
19468 / 16 / The listener with DNS name '%.*ls' for the availability group '%.*ls' is already listening on the TCP port %u. Please choose a different TCP port for the listener. If there is a problem with the listener, try restarting the listener to correct the problem
19469 / 16 / The specified listener with DNS name, '%.*ls', does not exist for the Availability Group '%.*ls'. Use an existing listener, or create a new listener.
19471 / 16 / The WSFC cluster could not bring the Network Name resource with DNS name '%ls' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different /
  • Check for associated errors like error 19476.

19476 / 16 / The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WFSC cluster and validate the network name and IP address with the network administrator. /
  • Check for associated errors like error 41009.

19477 / 16 / The availability group '%.*ls' already has a listener with DNS name '%.*ls'. Availability groups can have only one listener. Use the existing listener, or drop the existing listener and create a new one.
19486 / 16 / The configuration changes to the availability group listener were completed, but the TCP provider of the instance of SQL Server failed to listen on the specified port [%.*ls:%d]. This TCP port is already in use. Reconfigure the availability group listener
21872 / 16 / The availability group associated with Virtual Network Name '%s' has no replicas.
21880 / 16 / The virtual network name '%s' has been used to identify the redirected publisher for original publisher '%s' and database '%s'. The availability group associated with this virtual network name, however, does not include the publisher database.
21882 / 16 / The database '%s' at the redirected publisher '%s' for original publisher '%s' and database '%s' belongs to a HADRon availability group and must be redirected to its associated HADRon Virtual Network Name.
21883 / 16 / The query at the redirected publisher '%s' to determine whether the publisher database '%s' belonged to an availability group failed with error '%d', error message '%s'.
21884 / 16 / The query at the redirected publisher '%s' to determine the health of the availability group associated with publisher database '%s' failed with error '%d', error message '%s'.
21887 / 16 / The query at the redirected publisher '%s' to determine whether the publisher database '%s' belonged to an availability group failed with error '%d', error message '%s'.
21892 / 16 / Unable to query sys.availability_replicas at the availability group primary associated with virtual network name '%s' for the server names of the member replicas: error = %d, error message = %s.',
33445 / 16 / The database '%.*s' is a readable secondary database in an availability group and cannot be enabled for FILESTREAM non-transacted access.
33446 / 16 / The FILESTREAM database configuration cannot be changed for database '%.*s'. The database is either a mirror database in Database Mirroring, or is in a secondary replica of an AlwaysOn availability group. Connect to the server instance that hosts the pr
33449 / 10 / FILESTREAM File I/O access is enabled, but no listener for the availability group is created. A FILESTREAM PathName will be unable to refer to a virtual network name (VNN) and, instead, will need to refer to a physical Windows Server Failover Clustering (
33450 / 10 / FILESTREAM File I/O access is enabled. One or more availability groups ('%ls') currently do not have a listener. A FILESTREAM PathName will be unable to refer to a virtual network name (VNN) and, instead, will need to refer to a physical Windows Server F
35202 / 10 / A connection for availability group '%ls' from availability replica '%ls' with id [%ls] to '%ls' with id [%ls] has been successfully established. This is an informational message only. No user action is required. /
  • This is an informational message only. If there is a problem, then look for additional messages.

35205 / 16 / Could not start the AlwaysOn Availability Groups transport manager. This failure probably occurred because a low memory condition existed when the message dispatcher started up. If so, other internal tasks might also have experienced errors. Check the SQL
35206 / 10 / A connection timeout has occurred on a previously established connection to availability replica '%ls' with id [%ls]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role. /
  • If there is a login failed for domain\node$ account around same time, then it may be because NT AUTHORITY\SYSTEM account lacked the required permissions to failover the availability group. To failover the group to the other node, NT AUTHORITY\SYSTEM must have permission to connect to SQL (CONNECT SQL), failover the availability group (ALTER ANY AVAILABILITY GROUP) and execute sp_server_diagnostics (VIEW SERVER STATE).The NT AUTHORITY\SYSTEM account is used to automatically execute sp_server_diagnostics. Another option is to add the account to sysadmin role in SQL Server.
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITY\system];
35207 / 16 / Connection attempt on availability group id '%ls' from replica id '%ls' to replica id '%ls' failed because of error %d, severity %d, state %d.
35210 / 16 / Failed to modify options for availability replica '%.*ls' in availability group '%.*ls'. The specified availability group does not contain an availability replica with specified name. Verify that availability group name and availability replica name are c
35212 / 16 / The %ls operation is not allowed by the current availability-group configuration. This operation would exceed the maximum number of %d synchronous-commit availability replicas in availability group '%.*ls'. Change one of the existing synchronous-commit
35213 / 16 / The %ls operation is not allowed by the current availability-group configuration. This operation would exceed the maximum number of %d automatic failover targets in availability group '%.*ls'. Change one of the existing synchronous-commit replicas to th
35217 / 16 / The thread pool for AlwaysOn Availability Groups was unable to start a new worker thread because there are not enough available worker threads. This may degrade AlwaysOn Availability Groups performance. Use the "max worker threads" configuration option
35220 / 16 / Could not process the operation. AlwaysOn Availability Groups replica manager is waiting for the host computer to start a Windows Server Failover Clustering (WSFC) cluster and join it. Either the local computer is not a cluster node, or the local cluster
35221 / 16 / Could not process the operation. AlwaysOn Availability Groups replica manager is disabled on this instance of SQL Server. Enable AlwaysOn Availability Groups, by using the SQL Server Configuration Manager. Then, restart the SQL Server service, and retry t
35222 / 16 / Could not process the operation. AlwaysOn Availability Groups does not have permissions to access the Windows Server Failover Clustering (WSFC) cluster. Disable and re-enable AlwaysOn Availability Groups by using the SQL Server Configuration Manager. The
Top View