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

  • 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.

  • 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

  • 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.

  • 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

  • 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.

  • 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

  • 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
  • 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.

  • 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" 😉

  • Guys, finally I got the culprit login from audit login trace. It was really hectic but got the issue though. Feeling better now.

    Thanks.

  • how did you resolve the issue

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • SQL-DBA-01 - Thursday, May 25, 2017 9:45 AM

    Guys, 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...

  • Henrico Bekker - Friday, May 19, 2017 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.

    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