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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14938 Visits: 3722
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12416 Visits: 5242
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.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
SQL-DBA-01
SQL-DBA-01
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14938 Visits: 3722
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12416 Visits: 5242
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.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
SQL-DBA-01
SQL-DBA-01
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14938 Visits: 3722
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12416 Visits: 5242
Run Profiler against the secondary non-readable instance to catch attempted sessions and logins being made to the instance.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
GilaMonster
GilaMonster
SSC Guru
SSC Guru (415K reputation)SSC Guru (415K reputation)SSC Guru (415K reputation)SSC Guru (415K reputation)SSC Guru (415K reputation)SSC Guru (415K reputation)SSC Guru (415K reputation)SSC Guru (415K reputation)

Group: General Forum Members
Points: 415918 Visits: 47141
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14938 Visits: 3722
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
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103740 Visits: 18004
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14938 Visits: 3722
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