About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
Error 976 reads: "The target database, 'MyPublishedDB', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica isn't enabled for read access." The connection reached a replica where the database is part of an availability group but cannot be read. Check which replica the client actually reached, then check that replica's readable-secondary setting and the database's suspend state.
What the error means
Microsoft's page on replication with availability groups shows the full message, raised as error 976 at level 14, state 1:
"The target database, 'MyPublishedDB', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica isn't 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."
Microsoft's read-only access page explains the setting. By default, read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas. For the secondary role, ALLOW_CONNECTIONS takes three values: NO (no direct connections; the default), READ_ONLY (read-intent connections only) and ALL (all connections, for read access only).
Read-only routing is a separate layer. It sends read-intent connections made through the listener to a readable secondary, and it needs a listener, a read-only routing URL on each readable secondary, and a read-only routing list on the primary. The connection string must use TCP, set the application intent to read-only, name the listener and name a database in the group.
Likely causes
| Cause | What you see | How to confirm |
|---|---|---|
| The replica is not readable (ALLOW_CONNECTIONS = NO) | Error 976 when connecting straight to that secondary | secondary_role_allow_connections_desc in sys.availability_replicas |
| Data movement is suspended for that database | Error 976 on one database, others on the replica work | is_suspended = 1 in sys.dm_hadr_database_replica_states |
| A job, maintenance task or monitor is pointed at a secondary | The error appears in job history or monitoring, not in the application | The target server name in the job or tool's settings |
| Validation from replication against a secondary | Msg 21899 wrapping error 976 | Microsoft documents this as expected behaviour |
| Replicas on different major builds during an upgrade | A secondary is not readable until builds match | Microsoft's warning that only replicas on the same major build are readable |
| Routing is not in place, so read-intent traffic goes elsewhere | Reports hit the primary, or fail on a replica that cannot serve them | sys.availability_read_only_routing_lists and the connection string |
Prove the cause before changing anything
First find which replica the client reached. From the application server, check the target in the connection string. If it names a server rather than the listener, the client is going direct and the secondary's own setting decides.
On the primary, read each replica's setting and routing URL, using the query on Microsoft's configuration troubleshooting page:
SELECT replica_id, replica_server_name, secondary_role_allow_connections_desc, read_only_routing_url
FROM sys.availability_replicas;
Then the routing list:
SELECT * FROM sys.availability_read_only_routing_lists;
Then the database state on the replica that raised the error, with is_suspended and suspend_reason_desc from sys.dm_hadr_database_replica_states (the DMV needs VIEW SERVER STATE up to SQL Server 2019 and VIEW SERVER PERFORMANCE STATE from SQL Server 2022):
SELECT database_id, synchronization_state_desc, is_suspended, suspend_reason_desc
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1;
If the listener is the target, check that it is online on the primary: SELECT * FROM sys.dm_tcp_listener_states;, as Microsoft's page shows.
A good result shows the intended secondary as READ_ONLY or ALL, a routing URL present, the database not suspended, and the connection string naming the listener with ApplicationIntent=ReadOnly. A bad result is any one of these missing. For a job or script that must work on both roles, Microsoft shows a test using DATABASEPROPERTYEX([db name],'UpdateAbility') = N'READ_ONLY' to tell whether a database is a read-only secondary.
Fix
Replica not readable. Decide whether this replica should serve reads at all. Readable secondaries cost resources. Microsoft's pages note that read workloads use snapshot isolation, so row versions are kept in tempdb on the secondary, the primary adds 14 bytes of overhead to modified rows, and queries on the secondary can block the redo thread that applies schema changes. If that is acceptable, connect to the primary and run:
Disruptive step: changes replica connection settings. Impact: the secondary starts accepting read connections, which adds load and row-version storage to both replicas. Back-out: set ALLOW_CONNECTIONS back to NO, the default. Microsoft's pages do not say the 14-byte row overhead is removed afterwards.
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'sql02'
WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
READ_ONLY means read-intent connections only, so clients must set ApplicationIntent=ReadOnly. Use ALL only if clients cannot set the intent.
Suspended data movement. Fix the cause of the suspension, then resume on the instance hosting the database with ALTER DATABASE [SalesDb] SET HADR RESUME;. Risk: resuming starts catch-up traffic, and the page says the command returns before the resume completes. Back-out: ALTER DATABASE ... SET HADR SUSPEND, noting that the primary log cannot truncate while suspended. Microsoft's suspend page also says new connections to a suspended database on a readable secondary are not allowed until data movement resumes, when connections come through the listener and read-only routing.
Routing not configured. Set the routing URL first, then the list, because Microsoft says the URL must be set before the list:
Disruptive step: changes routing for every read-intent connection through the listener. Back-out: restore the previous list and URL, which you should record first.
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'sql02'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://sql02.contoso.com:1433'));
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'sql01'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST = ('sql02','sql01')));
Then set the client string, in the form Microsoft shows: Server=tcp:ag1-listener,1433;Database=SalesDb;ApplicationIntent=ReadOnly;MultiSubnetFailover=True. For sqlcmd, Microsoft gives sqlcmd -S AG_Listener,port -E -d AgDb1 -K ReadOnly -M, and warns that the capital -S sets the server name.
Jobs and monitors. Point them at the primary or the listener, or make them test updateability first. Replication validation: Microsoft says the 976 inside Msg 21899 is expected for secondaries that do not allow read access or require read intent, and that you should check the subscriber entries directly on the host instead.
Rolling upgrade. Wait for all replicas to reach the same major build before expecting reads from the secondary.
Confirm the fix
Connect as the application would and run a harmless read. Then run SELECT DATABASEPROPERTYEX(N'SalesDb','UpdateAbility'); in the same session. A result of READ_ONLY shows the session landed on a read-only secondary, which is what a read-intent route through the listener should produce. Confirm the DMV shows the database synchronising and not suspended. Check that queries are not blocked after enabling read access: Microsoft says active transactions that existed on the primary when read access was enabled must commit or roll back first, and queries on the secondary are temporarily blocked until then.
Example (illustrative): AG1 has sql01 as primary and sql02 as secondary with ALLOW_CONNECTIONS = NO. A reporting job pointed at sql02 directly runs every 15 minutes from 08:00 to 18:00 inclusive, which is 41 runs a day. Every run fails with error 976. The fix is READ_ONLY on sql02, then the routing URL on sql02 and the routing list on sql01, then ApplicationIntent=ReadOnly in the job's connection to ag1-listener, after which the job's 41 runs read from sql02. Pointing the job straight at sql02 with ApplicationIntent=ReadOnly set would also work, without the routing steps.
What to watch afterwards
- Check suspension: a database that suspends on the readable secondary will bring the error back for those reports.
- Watch redo lag on the readable secondary, since Microsoft documents that long read queries can block redo.
- Re-test routing after any failover or listener change, since the routing list takes effect only in the primary role and the URL only in the secondary role.
- Keep the connection string in one place so a change of intent is made once.
- Know the outage behaviour. Microsoft says readable secondaries can stay online when the primary is offline, but read-only routing does not work then because the listener is offline too, so clients must connect to the secondary directly. A direct connection needs the replica to allow it, which is the same setting this error points at.
- While a database is suspended, Microsoft's suspend page says existing connections to it on a readable secondary stay usable and new ones are refused until data movement resumes, for connections made through the listener and read-only routing. After a resume, expect the data to be as old as the moment of suspension until it catches up.
Read next
Related articles you may want next.
- HostingReporting load on SQL Server: readable secondaries, a reporting copy or the same instanceWhen month-end reports slow a business application down, there are three broad choices: manage reports on the same server, read from an availability group secondary, or keep a separate reporting copy. What each costs in licensing, data freshness and side effects.Read article
- HostingApplications reconnecting after a SQL Server failover: listener DNS, MultiSubnetFailover and retryWhy a business application can take far longer to reconnect after an availability group failover than the failover itself took, and which client settings, listener settings and retry practices shorten that time.Read article
- HostingHow to build and test SQL Server availability groups on VMwareCreate a controlled two-replica lab and validate database, listener, quorum and application recovery.Read article
Risks and limits
These commands have not been run or lab-tested. The 976 text is quoted from a replication page and the wording could differ for other contexts. Microsoft's pages name two causes for 976; the other rows in the table come from the setting and routing pages and are checks, not documented 976 causes. Enabling read access has costs on both replicas, and a readable secondary can return data that lags the primary.
Sources
- Configure Replication With Availability Groups — Microsoft Learn, page dated 2026-07-21, accessed 18 November 2026
- Configure read-only access to secondary availability group replica — Microsoft Learn, page dated 2024-09-05, accessed 18 November 2026
- Configure read-only routing for an availability group — Microsoft Learn, page dated 2026-08-25, accessed 18 November 2026
- Offload workload to secondary availability group replica — Microsoft Learn, page dated 2026-01-20, accessed 18 November 2026
- Troubleshoot Always On Availability Groups Configuration (SQL Server) — Microsoft Learn, page dated 2026-03-27, accessed 18 November 2026
- Suspend an availability database (SQL Server) — Microsoft Learn, page dated 2025-02-28, accessed 18 November 2026
- Resume an availability group database — Microsoft Learn, page dated 2025-02-28, accessed 18 November 2026
- sys.dm_hadr_database_replica_states (Transact-SQL) — Microsoft Learn, page dated 2026-07-09, accessed 18 November 2026
This article was originally published on .