0

I did setup the Ola Scripts sticking to https://www.brentozar.com/archive/2015/06/how-to-configure-alwayson-ag-backups-with-ola-hallengrens-scripts/

Options are:

@Databases = 'USER_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'Y',
@CopyOnly = 'Y',
@CleanupTime = 168,
@CheckSum = 'Y',
@LogToTable = 'Y'

The Backups run fine from the synchronous secondary.

SELECT sys.fn_hadr_backup_is_preferred_replica

gives back 1 on the secondary and 0 on the primary.

What strikes me is that on the primary I get not current backup info. The database properties display the date of my last manual backup ( e.g. when I joined a DB to the AG ).

Same for the T Logs. On the secondary I don't even get the DB properties as the replica is not enabled for read access.

As a result the Logs on the Primary are growing.

What am I missing ? Do I need to fail over to the secondary in order to give the former primary a chance to backup logs ?

1 Answer 1

1

The most recent backup time is pulled from msdb, which is not joined to the Availability Group (each replica has it's own copy of system databases). To get accurate information about the last backup time, you would need to check both replicas.

However, the fact that you are seeing log growth on the primary suggests that log backups may not be executing successfully on the secondary. You can run the following query on your secondary replica to confirm whether the backup is completing successfully:

SELECT database_name,MAX(backup_start_date) 
FROM msdb.dbo.backupset
WHERE type = 'L'
GROUP BY database_name

If that is showing a recent date, then the log growth on the primary may not be due to log backups. Run the following on your primary and see what the log_reuse_wait_desc says.

SELECT name,log_reuse_wait_desc
FROM sys.databases

If the log backups aren't actually being taken on the secondary (e.g. if the first query shows no recent log backups), then the first thing I'd do is investigate the output logs of the TLog backup job. Ola's jobs will automatically log detailed output to a file in the default SQL Log File directory, and any failures should be quite aparant there.

1
  • Hello, Followed your advice and the log_reuse_wait_desc was NOTHING. I did take a look at the logs again and found them to be nearly empty, so I did shrink he files. It would be really neat, if MS could incorporate backup with AG info. So when the Backup runs successful on the secondary to replicate the info to the primary or to the Dashboard of the AG.
    – Thomas D
    Commented May 22 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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