I'm running the Ola Hallengren Maintenance Solution and noticed the transaction logs weren't being backed up on the secondary, hence not being truncated, despite the AG backup preference being set to Prefer Secondary. If I run:
SELECT availability_groups.name,
dm_hadr_availability_replica_states.role_desc,
UPPER(availability_groups.automated_backup_preference_desc) as 'preference'
FROM sys.databases databases
INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
WHERE databases.name = 'EXT_CONFIG_DB'
on the secondary, I get:
name role_desc preference
SP-EXT-CONFIG SECONDARY SECONDARY
This is as expected. However if I then run:
SELECT sys.fn_hadr_backup_is_preferred_replica('EXT_Config_DB') as 'is_preferred_replica'
I get:
is_preferred_replica
0
This is wrong and should be set to 1
. As the fn_hadr_backup_is_preferred_replica
bit is set to 0
the Ola job doesn't backup or hence truncate the log. Searches seem to suggest lowercase instance names may be the issue, but all my instance names are upper case.
Can anyone suggest a resolution to fn_hadr_backup_is_preferred_replica returning 0 instead of 1?