Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_BlitzFirst and sp_BlitzCache - Sometimes fail when there are readable secondary databases on the server #2072

Open
Adedba opened this issue Jul 3, 2019 · 1 comment

Comments

Projects
None yet
2 participants
@Adedba
Copy link
Contributor

commented Jul 3, 2019

Version of the script
sp_BlitzFirst - Version = '7.6', VersionDate = '20190702';
sp_BlitzCache - Version = '7.6', VersionDate = '20190702';

What is the current behavior?
When I run the BlitzFirst collection to log output to tables using the following command on a Read intent secondary server it can error.

Command:

EXEC sp_BlitzFirst
@OutputDatabaseName = 'DBA',
@OutputSchemaName = 'dbo',
@OutputTableName = 'BlitzFirst',
@OutputTableNameFileStats = 'BlitzFirst_FileStats',
@OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats',
@OutputTableNameWaitStats = 'BlitzFirst_WaitStats',
@OutputTableNameBlitzCache = 'BlitzCache';

Error:
Msg 978, Level 14, State 1, Line 45
The target database ('ReadableDB') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

If the current behavior is a bug, please provide the steps to reproduce.
Whilst a query is running against a read intent secondary database either with a request or waiting task run sp_BlitzFirst or sp_BlitzCache, an easier way to demonstrate manually just using the DMVs is to:

  • Set an AG to have a Read-intent secondary (note that the read only route URLs and required config need to be present)

  • Using SSMS connect to the read intent secondary but in the connection window expand options > Additional Connection Parameters then enter "ApplicationIntent= ReadOnly"

  • Open a new query window and use a database of your choice from the AG you set to Read intent in step 1

  • Run a query relatively long running query in this window (3-5 seconds) which allows enough time to hop onto another query window and run the following query:

SELECT *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) pl
WHERE r.database_id IN (SELECT DB_ID('YourReadableDBname'));

As long as the query is still running in the other window you should see an error similar to this:
Msg 978, Level 14, State 1, Line 45
The target database ('ReadableDB') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.

What is the expected behavior?
To skip read intent databases to avoid the error occurring.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
SQL 2012 , possibly higher versions but do not have any servers with this config in place to test on higher versions.

@BrentOzar

This comment has been minimized.

Copy link
Member

commented Jul 3, 2019

Great bug report! Nice job. (For folks reading here and not Slack, Adrian's working on tweaks to the code.)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.