0

I am hoping someone may be able to offer some assistance with an issue that I do not seem to be able to work around.

We have 2 on-premise SQL Server 2019 machines in an always on availability group. We added a new server to the AVG (and Windows Cluster manager) (SQL Server 2019, same CU etc) with automatic seeding.

Initially this was synchronising databases, but we noticed an issue with some of them so had to remove them from the AVG, drop them from the replica's and then re-add them. This seems to be where we hit issues, as now none of the re-added databases will sync with either of the 2 secondary replica's.

We've tried pretty much everything (including adding a new blank db to see if it would sync - it didn't) to try and get it to sync, short of rebooting the primary.

I am feeling that we may need to reboot the primary (but not before setting failover to manual to avoid it failing over and leaving us with data loss).

The SQL Server Logs don't really tell us much either. The only log of note states "Transitioning from [SENDING_FILE_LIST] to [LIMIT_CONCURRENT_BACKUPS]" but I can't find much out there to suggest that this is the issue.

Has anyone experienced this issue and resolved it? Or have any suggestions of anything that I could try please?

Thanks.

1
  • What method are you using to bring a new database/replica up to date with the primary? AFAIK, the options are "automatic seeding" and "restores". If the former, have you tried disabling that and trying the latter?
    – Ben Thul
    Sep 28, 2023 at 14:46

1 Answer 1

2

There's not a lot of information here, so it's hard to say what's going on. There is a lot of information in the erorrlogs and I have a feeling that, unfortunately, the information is there but can look as though it isn't important if you're not sure what is and isn't important.

The SQL Server Logs don't really tell us much either. The only log of note states "Transitioning from [SENDING_FILE_LIST] to [LIMIT_CONCURRENT_BACKUPS]" but I can't find much out there to suggest that this is the issue.

In all versions of SQL Server including 2022, there is a limit of 5 concurrent seeding attempts. Whether or not this is an issue is hard to say because this is the only data point we have. Seeding could be attempted on other databases and failing for other reasons, due to the number of databases this will be one of the messages but is not really the issue. This makes me point out my first paragraph where there is much more data but we don't have it.

I am feeling that we may need to reboot the primary (but not before setting failover to manual to avoid it failing over and leaving us with data loss).

Who knows if this will help. If, somehow, the seeding tasks were leaked then it might. If, however, there is a permissions issues, asymmetric storage locations, etc., then it's not going to do anything. Deeper investigation is needed.

Automatic seeding, depending on the issues, can require a deep investigation not suitable for a site such as this. Having said that, some basic information can be gathered via the DMVs for seeding:

Additionally there are extended events sessions that can be used in a session:

sqlserver.hadr_automatic_seeding_state_transition,
sqlserver.hadr_automatic_seeding_timeout,
sqlserver.hadr_db_manager_seeding_request_msg,
sqlserver.hadr_physical_seeding_backup_state_change,
sqlserver.hadr_physical_seeding_failure,
sqlserver.hadr_physical_seeding_forwarder_state_change,
sqlserver.hadr_physical_seeding_forwarder_target_state_change,
sqlserver.hadr_physical_seeding_progress,
sqlserver.hadr_physical_seeding_restore_state_change,
sqlserver.hadr_physical_seeding_submit_callback
1
  • Thanks for your replies. Something in SQL Server was clearly upset. We decided to bounce the Primary and this resolved the sync issue! In terms how we were getting db's up to date, it was using automatic seeding.
    – AngryDog
    Sep 28, 2023 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.