Database Keeps Going into Recovery Pending State

Question

I have a SQL server database that has been running perfectly fine on my machine for about 6 months, a couple days ago out of nowhere it was inaccessible (Pending Recovery).

I did a bunch of Googling and have tried the following things to fix the issue but thus far restoring it from a previous backup is the only thing that seems to work.

I have tried (From SMS and SQLCMD):

ALTER DATABASE mydatabase SET EMERGENCY
ALTER DATABASE mydatabase set single_user
DBCC CHECKDB (mydatabase, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
ALTER DATABASE mydatabase set multi_user

Step #3 errors out with: "cannot open mydatabase is already open and can only have one user at a time"

Second try:

EXEC sp_resetstatus 'mydatabase';
ALTER DATABASE mydatabase SET EMERGENCY
DBCC CHECKDB ('mydatabase')
ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB ('mydatabase', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE mydatabase SET MULTI_USER

Step #5 errors out with the same error.

My question is what could be causing this in the first place and how can I fix it properly without having to do a restore twice a day.

Off-topic - belongs on [dba](https://dba.stackexchange.com/). But if you care about this database, you should hire some local expertise to help guide you. Diagnosing this problem will require very specific information that is difficult to discuss in a forum like this. Situations like this are often caused by disk drives that are starting to go bad. This [corruption discussion](https://www.sqlservercentral.com/articles/help-my-database-is-corrupt-now-what) might help. — SMor, Sep 11 '19 at 17:05
#smor @smor I apologize as I should have mentioned this originally, the issue is not occurring on our production database but simply on my development machine. I have other databases hosted on there as well and they seem to be unaffected by this issue — Josh, Sep 11 '19 at 17:21

score 1 · Answer 1 · answered Feb 04 '21 at 15:58

Database is already open and can only have one user at a time, this is error number 924. The complete error message looks like this:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Msg 924, Level 14, State 1, Line 1 Database ‘db_name’ is already open and can only have one user at a time.

The level 14 belongs to security level errors like a permission denied. It means that it cannot be open because someone is using it.

Use the sp_who or sp_who2 stored procedures. You can also use the kill command to kill the processes that are active in the database.

I also found this thread useful: How to fix Recovery Pending State in SQL Server Database?

score 0 · Answer 2 · answered Sep 11 '19 at 17:30

0

what could be causing this in the first place and how can I fix it properly without having to do a restore

The most likely cause is a a hardware or driver problem with your hard disk.

answered Sep 11 '19 at 17:30

David Browne - Microsoft

80,331
6
39
67

score 0 · Answer 3 · edited Feb 24 '22 at 22:42

In my case, I had databases set up on my local machine but on an external drive mapped to my hard drive. I have the external drive connected to my docking station all the time but I had to disconnect the hard drive and after I connected it again - the databases that are restored on the external drive went into Recover Pending mode.

In my case what helped me was to set the database offline in Microsoft SQL Server Management Studio by right clicking on the database - Tasks - Take Offline. The status of the database changes to Offline. After that bring the database online again by right clicking on the database - Tasks - Bring online.

The database was successfully recovered without any issues. But if the cause is different these steps may not help.

Take the database offline

Bring the database back online

Database Keeps Going into Recovery Pending State

3 Answers3