Sql Server Database Recovery Pending

0 views

Skip to first unread message

Billi Plancarte

unread,

Aug 3, 2024, 5:13:08 PM8/3/24

to rilaleres

All you had to do was kill the tape backup task, offline the DB and bring it online again. Detaching wasn't at all necessary. If you'd looked in the SQL error log, you'd have seen messages saying that SQL couldn't open the mdf because of OS error 112 (file in use by another process), which is a very good hint that some other process has the file locked. The backup process in this case.

No, it won't fix a database in Recovery_Pending mode (it's for a DB in SUSPECT), and 'don't worry' is a really silly thing to say right before telling people to run a command which will result in data loss!

recovery is in pending mode it means recovery is not completed due to some problem. if you are unable to figure out reason then then get expert help. use any other recovery tool to continue the process.

Do these files exist? Is the drive online and available? If so, try stopping and restarting the SQL service as it may be that SQL came up before the drive was available. That would have caused this error.

I have also noticed a consistency in the thread. Many are dealing with failed Windows\SQL Server updates who experience this issue, which is true in my case. I have two sister servers. One has never had an issue with failed updates, the other I have many failed updates, WMI issues and most databases will not come back online after quarterly reboots. The databases come back online with no issues on the sister server that has never had a failed update.

We have quite a few programs of our own design which communicate to our SQL Server (SQL Server 2014 Express). Every now and then they start blowing up all over the place; we run around trying to figure out what's wrong, only to open up SQL Server Management Studio and find that our database isn't running.

After running across this problem a few times, I've noticed that it only happens after we reboot the server- though not every time. Meaning that sometimes when the server boots up, SQL fails to do the same. An interesting fact is that we have multiple databases running on the SQL server and they don't go down together; one of the databases might say "(Recovery Pending)" while the other one works fine.

Have you got an on-access antivirus scanner running? Make sure you've got your exclusions set up correctly. Microsoft has documented this in KB309422. Also note that there are known issues with some products by McAfee and Sophos.

A recovery is typically performed by SQL Server upon startup when a database hasn't been shut down cleanly, f.e. because SQL server crashed, or because the server experienced a power outage; if this is not the case, then this could be caused by filesystem problems, disk issues or driver issues with storage controllers. You should check the system logs for unexpected shutdowns, or for disk troubles.

Go to Sql configuration manager > Sql Server Services >Select Service type (Sql Server) right click go to properties Select Logon as Check on This account and make sure your Account name is "NT SERVICE\MSSQL$SQLEXPRESS" then press start.

Alternatively, your application could run a simple "heartbeat" query every 10 minutes or so to keep SQL Server Express alive. SQL Server Express doesn't allow SQL Agent to be used, so a scheduled job wouldn't work. You could also schedule a sqlcmd.exe job using Windows Task Scheduler.

This status means recovery is stuck, not failed. To fix this, you need to understand the root cause, which could involve anything from increasing disk space to repairing or rebuilding database files.

I only recommend the REPAIR option as a last resort. Microsoft recommends restoring from the last known good backup as the primary method for recovering from errors reported by DBCC CHECKDB.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

This article describes the errors and limitations of an availability database in Microsoft SQL Server that's in a Recovery Pending or Suspect state and how to restore the database to full functionality in an availability group.

Assume that an availability database that is defined in an Always On availability group transitions to a Recovery Pending or Suspect state in SQL Server. If this occurs on the availability group's primary replica, database availability is affected. In this situation, you can't access the database through the client applications. Additionally, you can't drop or remove the database from the availability group.

For example, assume SQL Server is running and an availability database is set to the Recovery Pending or Suspect state. When you query the dynamic management views (DMVs) at the primary replica by using the following SQL script, the database might be reported in a NOT_HEALTHY and RECOVERY_PENDING state or in a SUSPECT state as follows:

Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.

Because of this error message, you may be compelled to fail over the database. After the database is failed over, the replica that owns the recovery pending database is in the secondary role. In this situation, you try to execute the following SQL script again to remove the database from the availability group at the secondary replica:

To take these actions, connect to the new primary replica, and then run the ALTER AVAILABILITY GROUP SQL script to remove the replica that's hosting the failed availability database. To do this, follow these steps.

These steps assume that the primary replica first hosts the damaged database. Therefore, a failover must first occur to transition the replica that's hosting the damaged database into a secondary role.

If the primary replica hosts the damaged database and is the only working replica in the availability group, the availability group must be dropped. After the availability group is dropped, your database can be recovered from a backup, or other emergency recovery efforts can be applied to restore the databases and to resume production.

On the instance of SQL Server to which the existing availability group listener is directing connections, create a new, empty availability group. To simplify this process, use the Transact-SQL command to create an availability group that has no secondary replica or database:

In the Roles pane, select the new availability group. In the bottom-middle pane, under the Resources tab, you should now see the new availability group and the listener resource. Right-click the new availability group resource, and then select Properties.

In SQL Server Management Studio, use Object Explorer to connect to the instance of SQL Server that hosts the primary replica of the new availability group. Select Always On High Availability, click the new availability group, and then select Availability Group Listeners. You should find the listener.

This makes sure that applications that use the listener can still use it to connect to the instance of SQL Server that is hosting the production databases without interruption. The original availability group can now be completely removed and re-created. Or the databases and replicas can be added to the new availability group.

If you re-create the original availability group, you should reassign the listener back to the availability group role, set up the dependency between the new availability group resource and the listener, and then reassign the port to the listener. To do this, follow these steps:

If you are hosting your availability group on a SQL Server Failover Clustered Instance (SQLFCI), you can associate the listener clustered resource with the SQLFCI clustered resource group while you drop and then re-create the availability group.

This makes sure that applications that use the listener can still use it to connect to the instance of SQL Server that hosts the production databases without interruption. The original availability group can now be removed and re-created. Or the databases and replicas can be added to the new availability group.

After the availability group is re-created, reassign the listener back to the availability group role. Then set up the dependency between the new availability group resource and the listener, and reassign the port to the listener:

For example, assume that your availability group listener is aglisten. The following Transact-SQL statement creates an availability group with no primary or secondary database, but it also creates a listener named aglisten. Applications can use this listener to connect.

Heloise Montini is a content writer whose background in journalism make her an asset when researching and writing tech content. Also, her personal aspirations in creative writing and PC gaming make her articles on data storage and data recovery accessible for a wide audience.

CEO at SalvageData Recovery, Bogdan Glushko has over 18 years of experience in high-security data recovery. Over the years, he's been able to help restore data after logical errors, physical failures, or even ransomware attacks, for individuals, businesses, and government agencies alike.