1

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

0

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
| improve this answer | |
  • I am aware of this issue. I am in contact with Microsoft about it. – Ola Hallengren Jul 17 '18 at 20:29
  • @OlaHallengren should it not return NULL if a backup hasn't happened on that node, especially if the node was the primary? – scsimon Jul 17 '18 at 20:35
  • 1
    It looks like sys.dm_db_log_stats only has information in a few columns (database_id, recovery_model, and log_backup_time) when you query it on the secondary replica. All other columns are NULL. I think that this is regardless of where log backups have been performed. I will write back when I get an update from Microsoft. – Ola Hallengren Jul 17 '18 at 20:50
  • 2
    I have been talking to Microsoft about this. Unfortunately it is by design. The documentation has been updated. "When running sys.dm_db_log_stats against a database that is participating in an Availability Group as a secondary replica, only a subset of the fields described above will be returned. Currently, only database_id, recovery_model, and log_backup_time will be returned when run against a secondary database." docs.microsoft.com/en-us/sql/relational-databases/… – Ola Hallengren Dec 30 '18 at 0:11

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.