Database in SQL server in recovery mode

Question

I have two databases (at the same server) in Microsoft SQL server. One of them can be successfully accessed remotely. However, the other not. It returns the following message in the error log:

Login failed for user 'adminUsr'. Reason: Failed to open the explicitly specified database 'alg_test.alg_test'. [CLIENT: ]

Error: 18456, Severity: 14, State: 38.

Then I go to Microsoft SQL server management and check the status of the database with:

SELECT databasepropertyex('alg_test.alg_test', 'STATUS')

and got this:

RECOVERING

It seems that the database is constantly recovering. How can I fix this? and finally geet access to the database remotely.

Answer 1

Check the SQL Server error log for related messages to see why the database is recovering. Common causes include:

1. The database was restored with the NORECOVERY option from full, differential, and log backups but RECOVERY was not specified on the last restore. The solution in this case is simply execute RESTORE <your database> WITH RECOVERY; to rollback uncommitted transactions and bring the database online.

2. The transaction log filled due to a large data modification operation and SQL Server is rolling the transactions(s) back to recover the database, which can take quite a bit of time. The error log will include recovery progress messages. It that's longer than you want to wait, it may be more expeditious to restore the database from backup(s). Be aware that if SQL Server is restarted during the database recovery process, recovery will restart from the beginning at service startup.