Cause:

When your database partition is full, the Microsoft SQL server sets the status of the database that can no longer grow to “Recovery Pending”. This status means that SQL can’t open the database and can’t lock the database files. This status is comparable to a database in offline mode.

Another cause is with problem with the drive. The drive is either missing or miss configured.

Fix:

Try restarting the SQL Server services first.

Run the following will either fix the database or tell you the problem…

ALTER DATABASE [MyDB01] SET ONLINE

if that does not work, try this…

RESTORE DATABASE [MyDB01] WITH RECOVERY
  • If it is a drive or file issue, fix the drive or file issue and restart SQL Server.
  • If the transaction log is out of space, you will need to fix the drive space issue first.
  • If the database is part of Windows Failover Clustering/AlwaysOn, make sure the Failover Cluster has been restarted and running.
  • After the issues above are fixed, the database will eventually go back to the “Synchronized” state.

Note: If issue is with transaction log filling up the drive and the database is in full backup mode, change the database to simple backup mode to bring the database back online.

ALTER DATABASE [MyDB01] SET RECOVERY SIMPLE;

ALTER DATABASE [MyDB01] SET ONLINE

Sources:

https://www.mssqltips.com/sqlservertip/5460/sql-server-database-stuck-in-restoring-state/

Last modified: February 15, 2022

Author

Comments

Write a Reply or Comment