Scenario, you have an availability group with active / passive (read only) set up and backups are set to be preferred on secondary. You install Ola on nodes A (RW) and B (R). A few days later, your T-Logs fill up and things are broken, what’s going on?
To cut to the chase, my problem was that I did not have T-Log backups installed on A and B, I only had them installed on A while I had the Full installed on A and B. So this means each weekend we would still get a full backup. When I checked the listener or node A/B, the full backups looked great. I didn’t realize immediately to check the T-Log job on the B node (secondary). Putting the T-Log backup process on A / B and scheduling them the same fixed it for me.
When I kicked the process off via the listener, it was running this command:
SELECT d.database_name, sys.fn_hadr_backup_is_preferred_replica (d.database_name) AS IsPreferredBackupReplicaNow FROM sys.availability_databases_cluster d
It basically returns a yes or no saying; Hey guys, I’m the node you want to take the backup on. If the job runs and sees a no, it ignores the database to backup on that node. Meanwhile the other node runs this same command and sees yes instead, it decides to backup the database. (The command is built into Ola scripts.)
A gotcha I ran into was running the job manually from the listener did not help my situation with T-Logs truncating because it was running the backup on the wrong node. So I did a lot of fruitless testing since I was starting the job from the listener. Hopping to the secondary and running the job there gave us T-logs and truncated them effectively.
I did run into a chain problem initially once I discovered the problem and solution. (Where I needed to take a new Full backup to base my Transaction Logs off of.) To determine exactly what the problem was, I ran this command:
SELECT name, log_reuse_wait_desc, recovery_model_desc FROM sys.databases
Here’s an explanation of the log_reuse_wait_desc column:
Explanation of log_reuse_wait_desc
I would recommend to follow Ola’s suggestions on setup and to read all documentation carefully when doing this for the first time as following his process should keep you problem free.
Brent Ozar also has some good tips and information for setting up Ola’s scripts on Availability Groups.