I have provided consultancy to this customer to deploy Always On Availability Group. We have designed backups in such a way that log backup happens on the secondary replica. This was working well but suddenly they started seeing this error.
An error occurred while processing ‘BackupMetadata’ metadata for database id 11 file id 1. [SQLSTATE 42000] (Error 3636)
Inconsistent metadata has been encountered. The only possible backup operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.
Above error has two error messages.
- Error 3636 – An error occurred while processing ‘%ls’ metadata for database id %d file id %d.
- Error 3046 – Inconsistent metadata has been encountered. The only possible backup operation is a tail-log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.
I asked more about the background of the issue and history. The had an interesting situation. One of the available databases showed enormous growth in the LDF file and they went ahead and added a new drive and additional file to mitigate the situation. This huge transaction was unavoidable for their business. When we looked at timings, we found that backup failure started after adding a new LDF file on the primary.
When I checked sys.master_files on primary and secondary, it was clear that secondary didn’t have the file yet. This was because the huge transaction was still getting replayed on the secondary replica. The REDO queue size of around 500 GB.
The error message appeared because the transaction which added the new file on primary replica didn’t apply yet on the secondary. We waited for redo queue to drain on this replica. As soon as we saw the same information about the files in the database, the log backup executed fine.
Reference: Pinal Dave (http://blog.SQLAuthority.com)