SQL Server production server - all databases are in recovery pending state

Question

All the databases in my SQL Server production server are in recovery pending state. I tried to execute different queries but they were of no use. Please help me as production work has been stopped at client side.

Tried to execute alter commands - but show error as following:

Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "G:\Data\MSSQL\Database.mdf". Operating system error 3: "3(The system cannot find the path specified.)". File activation failure. The physical file name "G:\Data\MSSQL\Data\Database_log.ldf" may be incorrect. Msg 945, Level 14, State 2, Line 1 Database 'Database' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details

Msg 5069, Level 16, State 1, Line 1

Answer 1

My team encountered this error many times for my clients & I know, It is not easy to manage in the Production server. In your case Error 5120 –This error comes when the database is in Read Only Mode. To fix this you can below code

USE [master]
GO
ALTER DATABASE [SQLAuthority] SET READ_WRITE WITH NO_WAIT
GO

After fixing 5120, you can process to fix "databases are in recovery pending state"

Answer 2

Execute these queries to fix SQL server database in recovery pending state:

ALTER DATABASE [DBName] SET EMERGENCY
GO
ALTER DATABASE [DBName] SET single_user
GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS
GO
ALTER DATABASE [DBName] SET multi_user
GO

EMERGENCY mode marks the SQL Server database as READ_ONLY, deactivates logging, and gives the permission to system admin only. This method is capable of resolving any technical issue and bringing the database back to the accessible state. The database will automatically come out of the EMERGENCY mode.

Answer 3

It might be because of following possible causes:

• Permissions

  Find your SQL Server instance in the services list and double-click it, then select the Log On tab. It is this log on account that must have sufficient permissions to write to the temporary backup folder location. Check the permissions on the temporary backup folder by right-clicking it in Windows Explorer, selecting Properties, then navigating to the Security tab. Make sure that the account SQL Server is using has explicit read/write permissions for this folder.

• Mapped Drives

  Use a fully qualified UNC path instead of a mapped drive letter.

• Lack Of Domain Trust

  You can resolve this issue by ensuring that a trust between the two domains is established. You may also need to configure the SQL Server service account with pass-through authentication between the domains.

Please refer more here for recovery db

Answer 4

Recovery Pending – If the SQL Server knows that database recovery needs to be run but something is preventing it from starting, the Server marks the db in ‘Recovery Pending’ state. This is different from the SUSPECT state because it cannot be said that recovery is going to fail – it just hasn’t started yet.

Execute the following set of queries:

ALTER DATABASE [DBName] SET EMERGENCY; GO
ALTER DATABASE [DBName] set single_user GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS; GO
ALTER DATABASE [DBName] set multi_user GO

Note: You can also read the Microsoft Warning on DBCC CHECKDB REPAIR ALLOW DATA LOSS.

Answer 5

Recovery pending means that for some reason SQL cannot run restart recovery on the database. Usually this is because the log is missing or corrupt.

Check to see if you can find the Database.mdf and Database_log.ldf files in the folder specified. Check your system has not run out of disk space.

This could be caused by a hard drive failure. You may need to do a full restore of your last full back, any differentials and then restore the logs up until the log error occurred.

See similar issue here