Sql-server – Log size is different between primary and secondary node with log_since_last_log_backup_mb

ola-hallengrensql servertransaction-log

We get log backups from the secondary node. But now I see that log_since_last_log_backup_mb in sys.dm_db_log_stats returns null for databases which are in Availability groups, so Ola Hallengren's log backup procedure with 

@LogSizeSinceLastLogBackup = 25,@TimeSinceLastLogBackup = 21000

limitations works on those databases. The backup process is currently working on the secondary node.

But I could not understand why it returns null? Have you encountered such a situation?

The following pictures show result of log size since last log backup on the primary and secondary nodes.

Primary Node

enter image description here

Secondary Node

enter image description here

Best Answer

I'd expect it to be NULL on the primary node if a backup has never happened on this node. You can confirm this by checking when the last log backup happened for those databases on the primary node:

SELECT   d.name,
         d.dbid,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
WHERE d.dbid in (19,34,21) --from your primary node
GROUP BY d.name, d.dbid
ORDER BY backup_finish_date DESC

And then on the secondary:

SELECT   d.name,
         d.dbid,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.sysdatabases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
WHERE d.dbid in (19,34,33) --from your secondary node
GROUP BY d.name, d.dbid
ORDER BY backup_finish_date DESC
Related Question