6

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.

Improve this question
2
  • As MSDN states: the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable. So eventually it will be online or in SUSPECT. If it takes too long to recover you could start SQL Server without going through recovery, I believe there's a Trace Flag for that. Commented Sep 16, 2016 at 9:32
  • Trace Flag 3608 can help you with that. However, proceed with care as the article states: Note: Do not use during normal operation. Commented Sep 16, 2016 at 9:39

3 Answers 3

Reset to default
11

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You had a better explanation.
if RESTORE DATABASE mydatabase WITH RECOVERY Not working for you, then try to make db offline then bring it online.
Perfect explanation. Suprisingly, after a huge data modification operation got cancelled, my database was stuck in recovery for ages - and continued to be regardless of restarts
5

Well you could try this script

RESTORE DATABASE mydatabase WITH RECOVERY

SO this will finish the recovery process with no backup files. If you are getting an error message 'database in use'. I think you shoudl try to stop the service, delete the databse and then Restore it with 'Recovery'

Improve this answer

2 Comments

what is this is normal recovery ?
Yes @TheGameiswar
1

Try this one :

Use [master]
GO

RESTORE DATABASE [DataBaseName] WITH RECOVERY
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.