Sql-server – SQL Server 2016 AlwaysOn DBCC CHECKDB on async replica

availability-groupsdbcc-checkdbsql serversql-server-2016

We have an AlwaysOn environment, which includes a replica in our DR site that is setup with async commit and readable secondary = No.

When we were running on SQL Server 2014 SP2 we were able to run DBCC CHECKDB against the databases on our DR replica. But since upgrading to SQL Server 2016 we're unable to and our weekly integrity job is failing with the error.

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

Obviously something has changed in 2016 to prevent this, I just don't know what?

Best Answer

I can say that the error message you are getting is because of a bug in SQL Server 2016. Microsoft knows about it and has fixed it in CU3 for SQL Server 2016 RTM.

Note: The user error is reported but the CHECKDB should continue as is.

See FIX: Error 976 occurs when you run the DBCC CHECKDB command on an unreadable secondary replica of SQL Server 2016 Availability Group

Workaround

You can take snapshot of the databases and can run checkdb on it. In this case it would not fail.Database snapshot is enterprise feature I am sure you are not using basic availability group

Related Question