SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The target database, '', is participating in an availability group and is currently not accessible...


The target database, '', is participating in an availability group and is currently not accessible for queries.

Author
Message
SQL-DBA-01
SQL-DBA-01
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4548 Visits: 3397
Hi,

I keep seeing this error in secondary node of the server. We dont have readable secondary. But the error only comes to just single environment.

Is there anything I can perform to fix the error?

Please suggest.

Error: 976, Severity: 14, State: 1.
The target database, '', 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.

Thanks.
Henrico Bekker
Henrico Bekker
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4614 Visits: 4732
That error will only present itself if you have an AG, and applications/users are trying to run update/delete/inserts to the secondary instance/replica DB.
Trace where those queries are coming from, and request the point to your listener name instead.
SQL-DBA-01
SQL-DBA-01
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4548 Visits: 3397
But when I right click on the database and check properties I see the below error which is kind of weird.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The target database, '', 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. (Microsoft SQL Server, Error: 976)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.4001&EvtSrc=MSSQLServer&EvtID=976&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Thanks.
Henrico Bekker
Henrico Bekker
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4614 Visits: 4732
Are you doing this on your non-readable secondary database? If yes, then you are seeing the correct message.
It has to be the Primary (RW) or at least Readable Secondary to view properties.
SQL-DBA-01
SQL-DBA-01
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4548 Visits: 3397
I am constantly checking the sys.processes to find if there is any user connection is being made on the database which is part of Alwayson,. but do not see any database been connected so far.

how to move further? I even started a XE capturing the audits but no luck

Thanks.
Henrico Bekker
Henrico Bekker
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4614 Visits: 4732
Run Profiler against the secondary non-readable instance to catch attempted sessions and logins being made to the instance.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)SSC Guru (115K reputation)

Group: General Forum Members
Points: 115972 Visits: 45520
SQL-DBA-01 - Friday, May 19, 2017 1:58 PM
But when I right click on the database and check properties I see the below error which is kind of weird.


Not at all weird. Only the principal replica and any read-only replicas can show properties, because that requires reading system tables from the database. If the database is unavailable (normal for the non-readonly secondaries), those tables can't be read and so the show properties will throw an error.

As per the error message:
Either data movement is suspended or the availability replica is not enabled for read accessthe availability replica is not enabled for read access.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SQL-DBA-01
SQL-DBA-01
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4548 Visits: 3397
Henrico Bekker - Friday, May 19, 2017 2:25 PM
Run Profiler against the secondary non-readable instance to catch attempted sessions and logins being made to the instance.

Ran XE but could not able to figure out the connection.


Thanks.
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27091 Visits: 17351
SQL-DBA-01 - Friday, May 19, 2017 1:32 PM
I keep seeing this error in secondary node of the server. We dont have readable secondary.

Then the message is correct, attempting to query a non readable secondary will throw this error


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
SQL-DBA-01
SQL-DBA-01
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4548 Visits: 3397
Guys, finally I got the culprit login from audit login trace. It was really hectic but got the issue though. Feeling better now.

Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search