SQL Server Error 976

Error number

976

Severity

14 (user-correctable error)

State

Not documented by Microsoft (not reproduced here)

Product

Microsoft SQL Server Database Engine

Versions

SQL Server 2016 to 2025 with Always On availability groups

Category

High Availability

Last reviewed

23 October 2026

What it means

Error 976 means you connected to a secondary replica of an availability group database that is not readable, either because the replica does not allow read access or because data movement to it is suspended.

On a secondary replica the database is constantly being updated from the primary, so it can be read only if the replica is configured for it (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY) or ALL) and data movement is running. Error 976 is raised when you USE the database or query it on a secondary where ALLOW_CONNECTIONS = NO, or where data movement is suspended. The name in quotes is the database; the server you are connected to is the secondary.

Level 14 is an access error: nothing is wrong with the data. Most often the client simply reached the wrong server, for example by connecting to a node name that became the secondary after a failover instead of to the availability group listener. Look at the message carefully: errors 978 and 979 are the related cases where the replica is readable but the connection's ApplicationIntent does not match its setting.

Common causes

• The application or user connects to a node name (or a DNS alias still pointing at the old primary) that is now a secondary replica, instead of to the listener.
• The secondary replica is configured with ALLOW_CONNECTIONS = NO, so no queries are allowed there.
• Data movement for the database is suspended on that replica (manually, or by the system after an error), so the secondary copy is not readable.
• A job or maintenance plan loops over every database on each node and runs queries, DBCC CHECKDB or backups against databases on a non-readable secondary.

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT ag.name AS ag_name, ar.replica_server_name, rs.role_desc, rs.connected_state_desc,
       ar.secondary_role_allow_connections_desc, ar.primary_role_allow_connections_desc,
       ar.read_only_routing_url
FROM sys.availability_replicas AS ar
JOIN sys.availability_groups AS ag ON ag.group_id = ar.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states AS rs ON rs.replica_id = ar.replica_id;

SELECT DB_NAME(drs.database_id) AS database_name, ar.replica_server_name, drs.is_local,
       drs.synchronization_state_desc, drs.is_suspended, drs.suspend_reason_desc,
       drs.database_state_desc
FROM sys.dm_hadr_database_replica_states AS drs
JOIN sys.availability_replicas AS ar ON ar.replica_id = drs.replica_id;

SELECT @@SERVERNAME AS server_name, SERVERPROPERTY('IsHadrEnabled') AS is_hadr_enabled;

SELECT ar.replica_server_name AS when_primary, rl.routing_priority,
       ar2.replica_server_name AS route_reads_to
FROM sys.availability_read_only_routing_lists AS rl
JOIN sys.availability_replicas AS ar ON ar.replica_id = rl.replica_id
JOIN sys.availability_replicas AS ar2 ON ar2.replica_id = rl.read_only_replica_id
ORDER BY when_primary, rl.routing_priority;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

Server=tcp:SalesAgListener,1433;Database=SalesDb;Integrated Security=true;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

ALTER DATABASE SalesDb SET HADR RESUME;

ALTER AVAILABILITY GROUP SalesAg
MODIFY REPLICA ON N'SQL02'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Examples

Using a database on a secondary that does not allow reads

Shown as documented in the message catalogue; not reproduced here because it needs an availability group. The state is the commonly reported one. On the primary, or on a secondary configured for read access with data movement running, the same statement succeeds.

-- Connected to SQL02, which is a secondary replica with ALLOW_CONNECTIONS = NO
USE SalesDb;

The target database, 'SalesDb', 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 databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

How to prevent it

Use the listener in every connection string, and keep DNS aliases out of the picture or update them as part of the failover runbook. Decide per replica whether it serves reads, and document the matching ApplicationIntent for reporting applications.

Write node-wide jobs to be role aware so they behave correctly whichever node is primary, and alert on suspended data movement so a secondary does not silently stop being readable.

Common questions

Learn more on CodeWithSQL

• RDS Multi-AZ and read replicas

Official documentation

• Configure read-only access to a secondary replica
• Offload read-only workload to a secondary replica
• Listeners, client connectivity and application failover
• sys.dm_hadr_database_replica_states (Transact-SQL)

Last technically reviewed 23 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.