Thursday, May 31, 2012 10:42 PM
When doing log backup on secondary database, I got this error:
Description: Log backup for database "test1_ag2" on a secondary replica failed because the last backup LSN (0x00000020:000000ad:0001) from the primary database is greater than the current local redo LSN (0x00000020:000000ac:0001). No log records need to be backed up at this time. Retry the log-backup operation later.
BACKUP LOG is terminating abnormally.
Since I would like to prevent log backup on secondary from happening if I can detect such condition beforehand, I am wondering how I could detect this and other similar conditions.
1) Which T-SQL or SMO, or system tabel to get primary database last backup LSN, and local redo LSN?
2) Are there any other similar conditions that would make log backup on secondary fail?
I can see sys.dm_hadr_database_replica_states can give me last_redone_lsn, but not sure this is same as last last redo LSN.
Thanks for your help!
- Edited by qzhang1 Friday, June 01, 2012 1:51 AM
Friday, June 01, 2012 5:37 AM
The last_redone_lsn is the same as the last redo LSN, so this is the first part of the equasion. The last backed up LSN can be found in the backupset table in MSDB.
Friday, June 01, 2012 3:46 PMThanks Lucifer! By any chance, do you know any other similar conditions which can cause log backup failure, but they are not harmful? Knowing the return error codes also helps. Basically I don't want to fail the backup for those scenarios.