Error 976: The Target Database Is Not Available on a Readable Secondary

Fix it now

The replica you reached is not offering that database for queries. Error 976 is published as the target database participating in an availability group and currently not accessible for queries, which covers both a secondary role that allows no connections and data movement that is suspended. Error 35250 is the neighbouring case: the connection to the primary is not active.

SELECT ar.replica_server_name, ars.role_desc,
       ar.secondary_role_allow_connections_desc
FROM sys.availability_replicas ar
JOIN sys.dm_hadr_availability_replica_states ars
  ON ar.replica_id = ars.replica_id;

SELECT DB_NAME(database_id), is_suspended, suspend_reason_desc,
       synchronization_state_desc, redo_queue_size
FROM sys.dm_hadr_database_replica_states;

1. Read the first result. NO in the secondary role column is the whole answer: that replica accepts no connections at all when it is a secondary, which is the documented default.
2. Change it if that is wrong: ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'NODE2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));. The accepted values are NO, READ_ONLY and ALL.
3. Read the second result. A suspended database gives you the documented reason directly, and the reason decides whether resuming is safe or will simply fail again.
4. Connect through the listener with ApplicationIntent=ReadOnly, which is what READ_ONLY requires, rather than connecting to the replica by name.
5. If you get 35250 instead, the secondary has lost its link to the primary. Check the endpoint and the network path before changing any replica setting.

Reading through the listener needs two things that are configured separately: the group must have a listener, and read-only routing must be configured. Setting the replica alone leaves you landing on the primary.

If read-intent connections now reach the secondary, stop here. If the setting will not stick, the last section covers the edition boundary.

Why it happens

A secondary database is continuously applying log records sent from the primary. To let readers in at the same time, SQL Server runs their statements against row versions rather than blocking them behind redo. Readability is therefore a deliberate setting rather than a default: it changes how the replica behaves and adds versioning overhead on the primary.

Two settings control access and they are easy to confuse. The replica’s secondary role decides whether it accepts connections at all: NO is documented as the default and means the secondary databases are not available for read access, READ_ONLY admits only connections whose application intent is ReadOnly, and ALL admits any connection but still only for reading. Separately, the primary’s read-only routing list decides where a read-intent connection arriving at the listener is actually sent. Get one right and not the other and you land on the primary, or on a replica that turns you away with 976.

The other three codes describe the group failing rather than refusing, and two of them are routinely described wrongly. Error 35250 is published as the connection to the primary replica not being active, so the command cannot be processed. Error 983 is published as being unable to access an availability database because the database replica is not in the PRIMARY or SECONDARY role, and telling you to try again later; it is a role message, and a cluster problem is only one of the things that can put a replica outside those roles.

Error 35262 is the one worth unlearning. Its published text says that the default startup of the database is being skipped because the database belongs to an availability group, that the availability group will start it, that this is an informational message only, and that no user action is required. It appears in the log every time an availability database starts. Treating it as evidence of a fault sends people to the cluster team over a routine line in the error log.

Full reference

Matching the symptom to the setting

The two role settings, side by side

Reading a suspended database honestly

sys.dm_hadr_database_replica_states publishes a reason for every suspension, and resuming without reading it is how the same outage happens twice in an afternoon.

What the synchronisation state is telling you

The documented values are NOT SYNCHRONIZING, SYNCHRONIZING, SYNCHRONIZED, REVERTING and INITIALIZING. A readable secondary sitting at SYNCHRONIZING with a large redo queue is readable and behind, which is a different conversation with the report’s owner than a replica that will not accept connections at all. Check the redo queue before you promise anyone current figures.

Backups and integrity checks on a secondary

• Taking backups on a secondary is one of the common reasons to make one readable, and it is one of the things a basic availability group is documented as not supporting.
• Integrity checks on secondary replicas are also outside what a basic availability group offers.
• A basic availability group is documented as two replicas with a single database, and it cannot be upgraded in place to an advanced availability group; it has to be dropped and recreated.
• Multiple basic availability groups can exist on one instance, which is how sites cover several databases inside the limit.

Every code this article covers

Confirm the fix worked

1. The replica query reports READ_ONLY or ALL for the secondary role on the replica you intend to read.
2. A connection through the listener with read intent returns the secondary’s name from SELECT @@SERVERNAME;.
3. sys.dm_hadr_database_replica_states shows the database synchronising and not suspended.
4. The redo queue is draining rather than growing while the report runs.
5. A representative report returns row counts you can reconcile against the primary.

Questions people ask about this