AG Database Stuck in Restoring

  • Hello,

    We have a 3 server AG setup. One of these instances is set to asynchronous since it's a DR manual failover server. We rebooted this machine recently and we expected the databases to resume synchronizing once it came back online however they did not. The error log is telling us the remote copy of the database has not been rolled forward to a point in time that is encompassed in the local copy of the database log. However the reboot was fairly quick, maybe 15 minutes so we where surprised that it couldn't just pick up where it left off. So we rebuilt the entire DR AG by dropping all the databases and re-adding the server to the group. All the databases started coming over as planned but this morning one database just says restoring. Looking at the log the most recent error for the database in question is, The database 'Reporting' is marked RESTORING and is in a state that does not allow recovery to be run. Can anyone suggest where to look or why this has been seemingly so sensitive to interruptions. Is there a way I can find what transaction log it's looking for? If I can find that out, I'm thinking I can try restoring that log manually and then try to resume on the database. Thanks for any suggestions.

  • Try to use this script to find out the logs that you may need to restore to the target. Looks like there is a backup job is running while you are trying to establish AG.

    If you are not fast enough, by the time you restored the logs, new log backups may be processed again. Try to find that one and see if you can pause it while establishing AG.

    SELECT database_name, backup_start_date, type, physical_device_name

    from msdb..backupset bs

    JOIN msdb..backupmediaset bms ON bs.media_set_id = bms.media_set_id

    join msdb..backupmediafamily bmf ON bmf.media_set_id = bms.media_set_id

    WHERE

    database_name = '<yourDB>'

    AND bs.backup_start_date >='fromwhatdate'

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply