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,
   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

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:


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?

Backup Preferences

Replica Properties

  • 1
    comments in this Connect Item might be helpfull – TheGameiswar Apr 3 '17 at 9:19
  • Please post the screenshots of your backup preferences tab or similar t-sql output along with current replica and database replica health states or each database in this AG. Without that, what you think should be a 1 and what should be a 1 are two different things. – Sean Gallardy Apr 3 '17 at 12:28
  • @SeanGallardy - screenshots added. – DiamondBeezer Apr 3 '17 at 13:06
  • Thanks - Can you add in your current collation as well :)? – Sean Gallardy Apr 3 '17 at 13:08
  • @DiamondBeezer,Hello, Your backup preference is 'secondar only'. But Where Ola Hallengren script is running. Primary replica or secondary replica. If your secript is running primary replica then always transaction log will create in primary. – Md Haidar Ali Khan Apr 3 '17 at 13:16

This is wrong and should be set to 1.

If you want it set to 1, then you should configure your backup preferences properly.

The reason it is not returning a 1 is because it shouldn't be based on your current preferences. Right now, all of your replicas have the exact same backup priority which means we're going to next look at replica names. Since UK-DR-SQL001\WSS sorts first, that should be the secondary (given that UK-SB-SQL01\WSS is your primary) that the function returns a 1 on.

If you want the backups to happen on a different server, then set the priority appropriately.

Here is an extremely simple repro showing that given your current settings, UK-DR-SQL001\WSS should be the replica which a 1 is returned.

    ReplicaName VARCHAR(40) NOT NULL,
    BackupPriority INT NOT NULL

INSERT INTO #Replicas(ReplicaName, BackupPriority) 
Values('UK-DR-SQL001\WSS', 50)

-- current case UK-SB-SQL01\WSS is the PRIMARY
-- prefer secondary is set
-- default collation assumed

SELECT ReplicaName, BackupPriority
FROM #Replicas
WHERE ReplicaName <> 'UK-SB-SQL01\WSS'
ORDER BY BackupPriority DESC --most important first
, ReplicaName ASC

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

It seems to be that this isn't setup or running on the DR server, or a backup would at least be attempted.

  • As per OP second screen shot, the 'UK-SB-SQL02\WSS' shows secondary replica. may be it's due to failover it has changed out. from secondary to primary. – Md Haidar Ali Khan Apr 3 '17 at 13:30
  • As per OP the primary is UK-SB-SQL01\WSS, you're looking at the screenshot wrong as the SS was taken from the secondary - please see the top where it says the primary instance is UK-SB-SQL01\WSS. @MdHaidarAliKhan – Sean Gallardy Apr 3 '17 at 13:32
  • Yes Sean. May be OP has sent wrong screen shot. Because in DashBoard screen shot only showing his secondary replica. There is not showing primary replica. – Md Haidar Ali Khan Apr 3 '17 at 13:36
  • It shows which replica is the primary - which is with the secondary health was what is needed. Please double check before you post comments with incorrect information. @MdHaidarAliKhan – Sean Gallardy Apr 3 '17 at 13:38
  • Sean - The DR server has no backup jobs. It is asynchronous, so there would be a risk of log backups being out of date. Take your point about alphabetical names (which your script proves, thanks), so I will change these priorities and report back. I inherited this HADR configuration. Further investigation today shows 5 of 7 AGs are set to Prefer Primary and 2 to Prefer Secondary. Part of my brief is to standardise... – DiamondBeezer Apr 3 '17 at 14:18

The interal code of the function fn_hadr_backup_is_preferred_replica utilizes the output of SELECT @@SERVERNAME to ensure the replica names match.

Ensure the output of that matches the replica name of your secondary. As well if you are using a case-senstive collation on your server it will need to be an exact match, or you get the same result.

  • select @@SERVERNAME as Server, rep.replica_server_name, ag.name FROM sys.availability_replicas rep join sys.availability_groups ag on rep.group_id = ag.group_id where ag.name = 'SP-EXT-CONTENT' and rep.replica_server_name like '%2\W%' (where to get a singleton result for illustration) gives: Server replica_server_name name UK-SB-SQL02\WSS UK-SB-SQL02\WSS SP-EXT-CONTENT so no discrepancy between @@servername and replica_server_name – DiamondBeezer Apr 3 '17 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.