Earlier I have written few blogs about automatic seeding feature. In this blog we would learn how to fix error: Automatic seeding of availability database ‘SQLAGDB’ in availability group ‘AG’ failed with a transient error.
Here are my earlier blogs about seeding failures.
SQL SERVER – AlwaysOn Automatic Seeding – Database Stuck in Restoring State
SQL SERVER – AlwaysOn Automatic Seeding Failure – Failure_code 15 and Failure_message: VDI Client Failed
In both scenarios, the database was started to restore, and it failed in the middle due to which it was in “Restoring” state. But in my client’s situation, the database was not getting listed in the databases folder in SSMS.
Automatic Seeding and Error
Here is the way to reproduce the error
CREATE DATABASE [SQLAuthority] ON PRIMARY (NAME = 'SQLAuthority', FILENAME = 'C:\Temp\SQLAuthority.mdf') LOG ON (NAME = 'SQLAuthority_log', FILENAME = 'C:\Temp\SQLAuthority_log.ldf') GO ALTER DATABASE SQLAuthority ADD FILE (NAME = 'SQLAuthority_D1', FILENAME = 'C:\Temp\D1.ndf') GO ALTER DATABASE SQLAuthority ADD LOG FILE (NAME = 'SQLAuthority_L1', FILENAME = 'C:\Temp\L1.ndf'), (NAME = 'SQLAuthority_L1', FILENAME = 'C:\Temp\L2.ndf') GO BACKUP DATABASE SQLAuthority TO DISK = 'SQLAuthority.bak'
This would create a database with two physical transaction log files with a same logical name. Once done, use Automatic Seeding and add a database to the availability group. It is not going to fail from the UI. Here is what you would see.
If you investigate and look at ERRORLOG file on secondary, here are the failure
- Error: 911, Severity: 16, State: 1.
- Database ‘SQLAuthority’ does not exist. Make sure that the name is entered correctly.
- Error: 4353, Severity: 16, State: 1.
- Conflicting file relocations have been specified for file ‘SQLAuthority_L1’. Only a single WITH MOVE clause should be specified for any logical file name.
- Error: 3013, Severity: 16, State: 1.
- RESTORE DATABASE is terminating abnormally.
- Automatic seeding of availability database ‘SQLAuthority’ in availability group ‘SQLAUTH-AG’ failed with a transient error. The operation will be retried.
This was a coincidence that I have written a blog about this error in a standalone scenario.
SQL SERVER – FIX: Msg 4353 – Conflicting File Relocations Have Been Specified for File. Only a Single WITH MOVE Clause Should be Specified for Any Logical File Name
I looked at the database and there were no duplicate logical names! I asked about this history of the database and if there was any file added and later removed and the answer was “Yes”.
I tried a lot of things, removing files completely, breaking AG, removing the database from AG. But none worked. I was not able to add the database to AG.
Finally, I used the workaround given in my blog to rename the logical file to some new name.
USE [SQLAuthority] GO ALTER DATABASE [SQLAuthority] MODIFY FILE (NAME=N'SQLAuthority_L1', NEWNAME=N'SQLAuthority_L2') GO
Once the command was executed, I was able to add the database to AG via automatic seeding. Hopefully, Microsoft would see this blog and fix duplicate logical file name issue. Until then you can use a workaround.
Reference: Pinal Dave (https://blog.sqlauthority.com)
In later CU-s(2016 SP 2 CU 5) if you remove the DB from the availability Databases restart the service on the secondary node, and add it again it will appear, and replicate.