As a part of my consulting, I observed that there is a lot of interest in using AlwaysOn availability group. I have deployed many AlwaysOn solutions from start-to-finish. Recently I encountered an issue and in this blog, we would discuss reason of an availability database to stay in restoring mode even after using automatic seeding.
If you have not heard earlier, one of the new features in AlwaysOn which was introduced in SQL Server 2016 was “automatic seeding”. In the latest version of SSMS, we can also see the option of seeding as below.
My client used the option and their database size was huge and seeding is supposed to work faster than a backup and restore method. We started seeding and I asked them to use below query to monitor the progress of seeding.
SELECT local_database_name ,role_desc ,internal_state_desc ,transfer_rate_bytes_per_second ,transferred_size_bytes ,database_size_bytes ,start_time_utc ,end_time_utc ,estimate_time_complete_utc ,total_disk_io_wait_time_ms ,total_network_wait_time_ms ,is_compression_enabled FROM sys.dm_hadr_physical_seeding_stats
They contacted me again and told that seeding is completed but the database is still shown in “Restoring” state.
When I attempted to join, I received an error
Failed to join the database ‘HiDB’ to the availability group ‘HiAG’ on the availability replica ‘(LOCAL)’. (Microsoft.SqlServer.Smo)
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
The remote copy of database “HiDB” has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)
This error is what tells us the exact cause of the issue. It looks like there is a log backup which happened in between seeding process. I asked them to use the blog and find SQL SERVER – Get Database Backup History for a Single Database
WORKAROUND/SOLUTION
In this case, after running the script from my blog, we found that a backup job which was taking log backup on another replica every 15 minutes. We disabled the job and started seeding again. This time it worked without any issue.
Have you encountered some error during AlwaysOn AG? Please share via comments and help others.
Reference: Pinal Dave (https://blog.sqlauthority.com)
13 Comments. Leave new
For larger databases I have found a combination of pushing to the secondary and pulling from the secondary node can help. On the secondary just click the join and when this finishes you have synchronized the database. Also beware that the owner of the secondary will become ‘sa’ especially if you use SQL logins.
Thanks for sharing this critical information about database ownership.
Besides Chris mentioning of the database owner, the trustworthy setting of the database is also disabled on the secondary replica server. Because we have lots of databases and they are added without my knowledge I’ve create an alert triggered SQL Job which changes the database owner as well as the trustworthy setting.
IF EXISTS (SELECT NULL FROM dbo.sysalerts WHERE [name] = ‘After AG Failover’)
EXEC dbo.sp_delete_alert @name=N’After AG Failover’;
GO
EXEC dbo.sp_add_alert @name=N’After AG Failover’, @severity=0, @enabled=1
,@message_id=1480 — Database Replica Role Change
,@delay_between_responses=0, @include_event_description_in=1
,@job_name=N’DbOwner_Trustworthy’;
GO
Hi, I am really happy to found such a helpful and fascinating post that is written in well manner. Thanks for sharing such an informative post.For more info visit our website.
I am also happy after reading this calfre.
Hi Pinal, I had this situation after adding 3rd node to the AG cluster. Most databases got seeded to the new node but some databases were stuck in Restoring mode, probably due to the log backup job. Now what should there be the solution without completely removing/re-adding database to the AG? The replica on the 2nd node is ok and is accessible for the queries..
You need to monitor seeding using DMVs. There are two dynamic management views (DMVs) for monitoring seeding: sys.dm_hadr_automatic_seeding and sys.dm_hadr_physical_seeding_stats.
It seems this failure is caused by broken log link when restoring log in secondaries. If the DB is huge and DB restore is finished, the following workaround might be faster.
1. Disable the log backup job in primary
2. Make a differential backup and log backup in primary.
3. Restore backups (step #2) in secondaries
4. Join AG
5. Enable log backup job in primary
Dave, I have a similar problem where we were able to remove the database, but the database we removed from the AG still shows under Availability Databases with a yellow triangle. We cannot seem to remove this and could use some more insight on this issue. Thanks!!
Mike,
What version build are you running? We have hit some serious problems on 2016 SP2 build 5201 talking to another 5201 server thru a linked server causing assertion errors and that can make the databases in the AG fail. Only restarting the primary node can recover the databases and AG.
Chris
Pinal,
I haven’t seen you in person for several years, but I utilize your advice all the time! Great work, and thanks for providing so many tips for us.
I may have a helpful hint for Maria above, but it also raises a question for me. I often (say 1 out of 5 databases as an estimate) receive a failure_state of 21 for seeding databases when viewing dm_hadr_automatic_seeding . Some of the databases which fail then retry by themselves, but not all do. I have found that by re-granting the CREATE ANY DATABASE permission to the group on the secondary node (or nodes) the database will then re-attempt to seed on its own and usually succeed. It could be the secondary log backup job you mention above, since I’m adding databases to an existing group, so I will see if it continues to happen when the job is disabled.
Either way, the command does trigger the seeding to restart.
Thanks Pinal,
Great stuff, we had a problem with seeding, looks like that was it.
HI Pinal,
I have a question – Auto seeding will enable the process for adding database to secondary whenever we create a new database in the primary db server?
I am looking for a solution to automate “adding databases to availability group once the new database is created in the primary db instance”. Could you please help me with that?
Thanks,
S Chand