I am using alwayson on my SQL 2012 databases. I am using ola hallengren scripts for backing up databases. Full & diff db backups work fine, but the log is not getting backed up. The tran log backup job doesn't error out too. Trying to figure out what I may be missing, did any one run into this kind of issu

Hello - Are you using BACKUP PREFERENCES in AlwaysOn ? And if so then remember that automated back-ups using Maintenance plans will honor the back-up preferences and back-ups may/will not happen on the node which is NOT preferred replica for taking the back-up

Automated back-ups now queries sys.fn_hadr_backup_is_preferred_replica to know whether the current node is preferred replica or not for taking back-up 

Take this example:

• You configured the back-up preference to SECONDARY ONLY
• The maintenance plan created on primary server will execute however with SQL Server 2012 AlwaysOn, this operation WILL NOT do anything because as per your back-up preferences back-ups will not happen on Primary Node. More importantly the job will reflect the status as successful however with no real action
• But the same maintenance plan will execute successfully on Secondary replica as per preferences

Hope this helps

This is probably due to your AlwaysOn Availability Group backup preference setting.

By default the group will be set to 'Prefer Secondary' which means that if you take a backup on the primary with Ola's scripts the backup (correctly) won't occur and you'll get a message towards the end of the output like this:.

Availability group role: PRIMARY  Availability group backup preference: SECONDARY  Is preferred backup replica: 

Try running the backup on the secondary to see if that works.

You can confirm the backup preferences by running this query:

select name, automated_backup_preference_desc from sys.availability_groups

Then I tried in primary, the full db backup job runs fine and does full backup of dbs in primary, which
I don't understand.
Since the group is set to "prefer secondary" for backups, then why the full backups is running fine in
primary, but not in secondary? I am confused!

Hi

This is the expected behavior. In this scenario the backup preference relates to the LOG backups. It's only possible to do a COPY_ONLY full backup on the secondary (differentials are not supported either).

More details here
https://msdn.microsoft.com/en-us/library/hh245119(v=sql.110).aspx

• Edited by jmcmullen 12 hours 43 minutes ago added link

Then I tried in primary, the full db backup job runs fine and does full backup of dbs in primary, which
I don't understand.
Since the group is set to "prefer secondary" for backups, then why the full backups is running fine in
primary, but not in secondary? I am confused!

Hi

This is the expected behavior. In this scenario the backup preference relates to the LOG backups. It's only possible to do a COPY_ONLY full backup on the secondary (differentials are not supported either).

More details here
https://msdn.microsoft.com/en-us/library/hh245119(v=sql.110).aspx

• Edited by jmcmullen Friday, June 26, 2015 6:22 PM added link

Let me add something.

• Backup preference would not come into picture when you take manual backup. This means that you would not get any error if you run backup log command on any replica.
• Full backup is not allowed on secondary replica.
• Differential backup is not allowed on secondary replica.
• Transaction log backups would form a chain and can be take from any replica.

my questions. Please post output of

DBCC SQLPERF('Logspace')
go
select name, log_reuse_wait_desc
from sys.databases
go