May 19, 2017 at 1:32 pm
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.
May 19, 2017 at 1:37 pm
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
May 19, 2017 at 1:58 pm
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.
May 19, 2017 at 2:08 pm
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
May 19, 2017 at 2:15 pm
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.
May 19, 2017 at 2:25 pm
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
May 19, 2017 at 2:28 pm
SQL-DBA-01 - Friday, May 19, 2017 1:58 PMBut 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
May 22, 2017 at 3:45 pm
Henrico Bekker - Friday, May 19, 2017 2:25 PMRun 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.
May 25, 2017 at 9:35 am
SQL-DBA-01 - Friday, May 19, 2017 1:32 PMI 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" 😉
May 25, 2017 at 9:46 am
Guys, finally I got the culprit login from audit login trace. It was really hectic but got the issue though. Feeling better now.
Thanks.
May 26, 2017 at 4:51 am
how did you resolve the issue
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 23, 2017 at 5:44 pm
SQL-DBA-01 - Thursday, May 25, 2017 9:45 AMGuys, finally I got the culprit login from audit login trace. It was really hectic but got the issue though. Feeling better now.
How did you solve the issue...i am having the sameproblem but during midnight and its not possible to run profiler that time...please share your solution...
September 14, 2018 at 9:34 am
Henrico Bekker - Friday, May 19, 2017 2:08 PMAre 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.
Hi Henrico, Thanks for your valuable comment...
I had similar issue, there was actual 2 servers for reporting services,but only one was synchronized ... For the server which non-synch we are getting this issue.. But Reporting Manager Got Connected to the primary DB.
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, 'ReportServer', 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=12.00.4457&EvtSrc=MSSQLServer&EvtID=976&LinkId=20476
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply