marcingminski/sqlwatch

Stored procedure usp_sqlwatch_logger_index_usage_stats trips over availability group databases.

Closed this issue · 1 comments

SanSalpeter commented

Did you check DOCS to make sure there is no workaround?
https://sqlwatch.io/docs/

Describe the bug
Stored procedure usp_sqlwatch_logger_index_usage_stats trips over availability group databases.

To Reproduce
Steps to reproduce the behavior:

  1. Go to..
  2. Click this..

Expected behavior
Skip databases not readable to be skipped in logging index usage stats.
NEW:
/* step 1 , collect indexes from all databases */
declare c_db cursor for
select [name] --, hars.role_desc, ar.secondary_role_allow_connections_desc
from sys.databases d
LEFT JOIN sys.dm_hadr_availability_replica_states hars ON d.replica_id = hars.replica_id
LEFT JOIN sys.availability_replicas ar ON d.replica_id = ar.replica_id
where database_id > 4
AND state_desc = 'ONLINE'
AND ( (hars.role_desc = 'PRIMARY' OR hars.role_desc IS NULL)
OR (hars.role_desc = 'SECONDARYY' AND ar.secondary_role_allow_connections_desc IN ('READ_ONLY','ALL')))
and [name] not like '%ReportingServer%'

Old:
/* step 1 , collect indexes from all databases */
declare c_db cursor for
select [name] from sys.databases
where database_id > 4 and state_desc = 'ONLINE'
and [name] not like '%ReportingServer%'

Screenshots
If applicable, add screenshots to help explain your problem.

Windows Server (please complete the following information):
Windows NT 6.3

SQL Server (please complete the following information):
Microsoft SQL Server 2012 (SP4-OD) (KB4091266) - 11.0.7469.6 (X64) Feb 28 2018 17:47:20 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: )

SQL Server Management Studio (SSMS -> about -> copy info):
Microsoft SQL Server Management Studio 14.0.17224.0
Microsoft Analysis Services Client Tools 14.0.1016.244
Microsoft Data Access Components (MDAC) 10.0.14393.0
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.11.14393.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.14393

SQLWATCH version (from DACPAC or from sysinstances)

  • 2.0.0.0

Additional context
Add any other context about the problem here.

marcingminski commented

Thank you , I will address this in the next release