Fix Recovery Pending State in SQL Server Database

How to Fix Recovery Pending State in SQL Server Database?

First you Mark your SQL Database in Emergency Mode and Start Forceful Repair

Follow these steps to resolve SQL server database issue:

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

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

Eg. 
 
ALTER DATABASE attaakwal SET EMERGENCY;
GO
ALTER DATABASE attaakwal set single_user
GO
DBCC CHECKDB (attaakwal, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE attaakwal set multi_user
GO

just click and refresh on the database, you will see, it will be looks good.

Reasons :-

• Insufficient disk space.
• Sudden shutdown the SQL server
• It maybe when you restoring the old backup
• It maybe when you change the state from offline to online etc...


 

To Check the Database stats :-

SELECT name, state_desc from sys.databases

GO

To check the process of databases:

    exec sp_who

To check the process of specific database:

     SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('YourDatabase')

To kill the process:

    KILL spid  

    eg kill 61 

Cheers...!!!