{"action":"created","issue":{"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1318","repository_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit","labels_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1318/labels{/name}","comments_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1318/comments","events_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1318/events","html_url":"https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1318","id":279395675,"number":1318,"title":"sp_foreachdb Breaks when an instance has AG non-readable secondaries","user":{"login":"amtwo","id":9381581,"avatar_url":"https://avatars1.githubusercontent.com/u/9381581?v=4","gravatar_id":"","url":"https://api.github.com/users/amtwo","html_url":"https://github.com/amtwo","followers_url":"https://api.github.com/users/amtwo/followers","following_url":"https://api.github.com/users/amtwo/following{/other_user}","gists_url":"https://api.github.com/users/amtwo/gists{/gist_id}","starred_url":"https://api.github.com/users/amtwo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/amtwo/subscriptions","organizations_url":"https://api.github.com/users/amtwo/orgs","repos_url":"https://api.github.com/users/amtwo/repos","events_url":"https://api.github.com/users/amtwo/events{/privacy}","received_events_url":"https://api.github.com/users/amtwo/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2017-12-05T14:31:30Z","updated_at":"2017-12-05T14:39:56Z","closed_at":null,"author_association":"NONE","body":"**Do you want to request a *feature* or report a *bug*?**\r\n*Bug*\r\nVersion 1.9.5 - 20171115\r\n\r\n**What is the current behavior?**\r\nIf you run sp_foreachdb on an instance with AG secondary replicas that are *not* readable, it fails with \r\nMsg 976, Level 14, State 1, Line 35\r\nThe target database, 'IAmInAnAvailabilityGroup', 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.\r\n\r\nThe database state is \"ONLINE\" so you cannot use the existing filter to ignore non-readable secondary replicas. The pertinent information is not contained in \r\n\r\n**If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem via http://sqlfiddle.com**\r\n\r\nConfigure an AG where the secondary replicas do not accept connections, then run this query on a server with secondary replicas:\r\nEXEC sp_foreachdb @suppress_quotename = 1, @state_desc = 'ONLINE', @command = 'USE [?]'\r\n\r\n\r\n**What is the expected behavior?**\r\n\r\nUsing either existing or new parameters, I should be able to run sp_foreachdb on an instance with non-readable secondary replicas. I would be inclined to use the existing @state_desc = 'ONLINE' filter. Technically the DB's state is ONLINE, but it is effectively offline, since it is unavailable for querying. \r\n\r\nThis fix would need to include version-aware logic, since the necessary AG DMOs are not present in all supported versions.\r\n\r\n**Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?**\r\n\r\nsp_foreachdb has never been AG-aware"},"comment":{"url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/comments/349323279","html_url":"https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1318#issuecomment-349323279","issue_url":"https://api.github.com/repos/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/1318","id":349323279,"user":{"login":"BlitzErik","id":11590415,"avatar_url":"https://avatars2.githubusercontent.com/u/11590415?v=4","gravatar_id":"","url":"https://api.github.com/users/BlitzErik","html_url":"https://github.com/BlitzErik","followers_url":"https://api.github.com/users/BlitzErik/followers","following_url":"https://api.github.com/users/BlitzErik/following{/other_user}","gists_url":"https://api.github.com/users/BlitzErik/gists{/gist_id}","starred_url":"https://api.github.com/users/BlitzErik/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/BlitzErik/subscriptions","organizations_url":"https://api.github.com/users/BlitzErik/orgs","repos_url":"https://api.github.com/users/BlitzErik/repos","events_url":"https://api.github.com/users/BlitzErik/events{/privacy}","received_events_url":"https://api.github.com/users/BlitzErik/received_events","type":"User","site_admin":false},"created_at":"2017-12-05T14:39:56Z","updated_at":"2017-12-05T14:39:56Z","author_association":"MEMBER","body":"Doing an FRK release today. Can you test code quickly?"}}
{"actor":{"display_login":"BlitzErik"}}