While setting up a new replica in an Availability Group on our reporting cluster a few of the databases were not seeded correctly. I tried manually seeding them using a recent backup and applying the latest log file backups, which worked for most of the databases but a few of them are stuck in Initializing / In Recovery on startup in SSMS. Even restarting the SQL Server service didn't help. Here are the steps I have tried and the resulting errors:

--Get a list of database not Online and not Restoring
select name,state,state_desc from sys.databases where state not in (0,1)

name                        state   state_desc
StackExchange.Aviation.Meta 2       RECOVERING

drop database [StackExchange.Aviation.Meta]
Msg 3702, Level 16, State 4, Line 21
Cannot drop database "StackExchange.Aviation.Meta" because it is currently in use.

RESTORE LOG [StackExchange.Aviation.Meta] FROM DISK = '\\Backups\SQL\_Trans\StackExchange.Aviation.Meta_LOG_20170901_043251.trn' WITH NORECOVERY;
Msg 1222, Level 16, State 24, Line 11
Lock request time out period exceeded.
Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.

RESTORE DATABASE [StackExchange.Aviation.Meta] FROM DISK = '\\Backups\SQL\StackExchange.Aviation.Meta_FULL_COPY_ONLY_20170901_040130.bak' WITH NORECOVERY;
Msg 1222, Level 16, State 24, Line 12
Lock request time out period exceeded.
Msg 3013, Level 16, State 1, Line 12
RESTORE DATABASE is terminating abnormally.

RESTORE DATABASE [StackExchange.Aviation.Meta] with recovery
Msg 1222, Level 16, State 24, Line 11
Lock request time out period exceeded.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally.

Msg 5061, Level 16, State 1, Line 5
ALTER DATABASE failed because a lock could not be placed on database 'StackExchange.Aviation.Meta'. Try again later.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.

Even stopping and restarting the sql service did not help, as it comes back in the same stuck state. What else can I do to re-seed this database?

4 Answers 4


Well, when I saw the error messages I figured the database was not added to the Availability group, but after checking the Availability Databases in SSMS on the new replica it was there with a pause symbol next to it. This means it was added but the data movement has stopped. I tried resuming data movement, but it was too far behind. So instead I had to remove it from the availability group and manually apply log files so it could start replicating again.

-- Remove database from Availability Group:    
Alter Database [StackExchange.Aviation.Meta] SET HADR OFF;

-- Apply t-logs to catch up. This can be done manually in SSMS or via:
RESTORE LOG [StackExchange.Aviation.Meta] FROM DISK = '\\Backups\SQL\_Trans\StackExchange.Aviation.Meta_LOG_20170901_043251.trn' WITH NORECOVERY;

-- Re-join database to availability group
ALTER DATABASE [StackExchange.Aviation.Meta] SET HADR RESUME;

I should have noticed it was added, but the AG has over 300 DBs and was still in the process of seeding so it was missed among the noise. The "Lock request time out" error message also made me think that something had gone wrong in the seeding, but it was just a poorly worded message indicating the database was already part of the Availability Group.

  • 300 dbs in your AG? You're gonna have a bad time. I see major threadpool issues in your future.
    – Nic
    Commented Sep 1, 2017 at 16:18
  • @Nic We have 364 DBs in the production SENetwork_AG, which can keep up just fine after we bumped the workers EXEC sp_configure 'max worker threads', 2400;. This is now expanding that AG to a new reporting cluster using a Distributed AG, but due to different hardware/disks or tuning of distributed AGs it is having difficulty keeping up. Gonna have to file some issues with Microsoft to see why it is having so many issues.
    – Greg Bray
    Commented Sep 1, 2017 at 18:52
  • Look forward to them telling you that there are too many dbs in the AG (I could almost write the email they'll say about blah blah support blah blah resources). Async in the distributed AG?
    – Nic
    Commented Sep 1, 2017 at 20:46
  • @Nic We've ran into a few issues before but gotten thru to the right people at Microsoft to get things addressed. Often just a missing index on system DMVs or a small tweak to tune an algorithm/process. Definitely using async on both the Distributed AG and source/target AGs. I did a GroupBy session on our setup earlier this year at groupby.org/conference-session-abstracts/…
    – Greg Bray
    Commented Sep 2, 2017 at 22:11

I had same "problem" on Windows server 2019, SQL server enterprice 2019.

These are the steps I did in order to add large database in AG:

  • Made full backup of DB and restore it on secondary server;
  • Tomorow I took DIFF backup and restore it with no recovery over existing DB on secondary;
  • Stop log backup on primary server and restored all logs over existing secondary DB.
  • Over add database to Avaibility group gui, starting add database to Avability Group with JOIN ONLY option;
  • DB added to ag !

After I take a look on AG dashboard, all looks fine, but secondary database was in Initializing / In Recovery status.

I did nothing but waiting to see what will happen after some time. All went good and secondary database was synchronized well after 10 minutes!


Obviously I've missed the boat here, but in case anyone else runs into this issue, I think the database is still recovery.
I had one move to "Initializing" status, with nothing being written to the SQL error log after that point (last entry was like "Recovery of database is 36% complete..."). I suspect the database was still restoring, but can't confirm since I switched the synchronous replica to async mode and restarted the host at the point, and after a 30 min recovery, the database in the AG started synchronizing.


ISSUE On the DR server a database was showing "initializing\in recovery" for an extended period of time. When looking at the blocking, you could see this process SPID "hadr_recovery_wait_for_undo" You can't stop this SPID, and so you can't add the database to the AG, resume data movement, drop or restore the database as it is in use. Rebooting the secondary replica had no effect.

RESOLUTION Disable log backups on primary. Perform a full\log backup on primary.

Reboot the secondary and quickly drop the database when the servers comes back online. Restore the full\log backup on secondary. Add database to HADR on secondary.

Enable log backups on primary.

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.