search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.9k stars 754 forks source link

User Database Integrity Check jobs fail on secondary AG servers #412

Closed shaikaijaz closed 57 minutes ago

shaikaijaz commented 4 years ago

Description of the issue DatabaseIntegrityCheck for User databases is failing on the secondary server in SQL ALwaysOn AG. This was working fine until recent windows patches were installed on the server. Post that, this job is failing. Other jobs such as Backups are running successfully.

SQL Server version and edition Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) Jan 23 2020 21:00:04 Copyright (C) 2017 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Version of the script Ran latest script but still the same issue.

What command are you executing? sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DatabaseName -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b

What output are you getting? Database: [ProCal58S] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Availability group: WSSQLPRD17AG Availability group role: SECONDARY Date and time: 2020-09-17 02:36:26 Command: DBCC CHECKDB ([DatabaseName]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY Msg 976, Level 14, State 1, Server ServerName, Line 1 The target database, 'DatabaseName', 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. Process Exit Code 1. The step failed.

shaikaijaz commented 4 years ago

@olahallengren , Can you please help here.

olahallengren commented 4 years ago

This looks like a bug in SQL Server. It should work to perform checkdb on secondary replicas in Enterprise Edition.

shaikaijaz commented 4 years ago

It is working perfectly on other AlwaysOn servers but not this.

On Sat, 24 Oct 2020, 10:25 pm Ola Hallengren, notifications@github.com wrote:

This looks like a bug in SQL Server. It should work to perform checkdb on secondary replicas in Enterprise Edition.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/olahallengren/sql-server-maintenance-solution/issues/412#issuecomment-715992727, or unsubscribe https://github.com/notifications/unsubscribe-auth/ARA7YZXDKS7XJK4IL6JAMF3SMMBHZANCNFSM4RQOQEOA .

olahallengren commented 4 years ago

Are the other server also Enterprise Edition? Do all the servers have the same CU?

olahallengren commented 3 years ago

Could you try executing this script on both replicas?

USE [master]
GO
DBCC CHECKDB ([DatabaseName]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
GO
olahallengren commented 3 years ago

Could you try installing the latest CU?

olahallengren commented 2 years ago

Is this issue still happening?

DavidWiseman commented 2 years ago

This issue can occur on Enterprise edition if you have performed an edition upgrade from Standard edition. This occurs on SQL 2019 with CU 17. Running the DBCC command manually results in the same error.

You also get this error if you try to enable readable secondaries - even though you are now on enterprise edition:

"The specified command is invalid because the Always On Availability Groups allow_connections feature is not supported by this edition of SQL Server. For information about features supported by the editions of SQL Server, see SQL Server Books Online."

The solution is to drop and re-create the availability groups.

olahallengren commented 57 minutes ago

Thank you, @DavidWiseman. This is by design in SQL Server.

"Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition."

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups?view=sql-server-ver16