Database status is in recovery pending | How to resolve cannot open database test requested by the login?

by
This article help to resolve, Cannot open database “test” requested by the login. The login failed. Login failed for user ‘ABC\Test’. or Database status is in recovery pending

Why we get error Cannot open database “test” requested by the login. The login failed. Login failed for user ‘ABC\Test’

Sometime due to heavy SQL queries or transaction database file drive space get full and there is no space for database to insert records. Due to this database goes into recovery pending state. If user try to access those databases when SQL service come back it failed with error Cannot open database “test” requested by the login. The login failed. Login failed for user ‘ABC\Test’. Reason for this is SQL server unable to put that database in online state may be due to large running transaction.

How to check recovery status of database?

If you verify database recovery in SQL error log , you will find that recovery of database started but it got stuck in between and also not processing well.

You can check status of database recovery completion using below script. This script will give percent completed, when recovery started and if database recovery process is moving or stuck.

DECLARE @DBName VARCHAR(64) = 'DB name'

DECLARE @DBRecoveryStatus AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @DBRecoveryStatus
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName

INSERT INTO @DBRecoveryStatus
EXEC master..sp_readerrorlog 0, 1, 'Recovery completed', @DBName

SELECT TOP 1
    @DBName AS [DBName]
   ,[LogDate]
   ,CASE
      WHEN SUBSTRING([TEXT],10,1) = 'c'
      THEN '100%'
      ELSE SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4)
      END AS CompletePercent
   ,CASE
      WHEN SUBSTRING([TEXT],10,1) = 'c'
      THEN 0
      ELSE CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0
      END AS Minutesremain
   ,CASE
      WHEN SUBSTRING([TEXT],10,1) = 'c'
      THEN 0
      ELSE CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0
      END AS Hoursremain
   ,[TEXT]
FROM @DBRecoveryStatus ORDER BY CAST([LogDate] as datetime) DESC

How to resolve error Cannot open database “test” requested by the login. The login failed. Login failed for user ‘ABC\Test’ OR Database status is in recovery pending

To resolve this error, most of the cases below solution work.

Step 1 : Verify if disk have sufficient free space available. If now create free space by shrinking log files or deleting old backup files from drive. If there is no scope add more space on drive.

Step 2 : once you have sufficient disk space , run below query to make database online. Database some does not come in online state without manual intervention.

ALTER DATABASE DBNAME SET ONLINE

This process may take time to complete depending on size of database. Once this query completed database will be in online state.

Leave a Comment