Peter Linke Last updated: Nov 22, 2016
3 min read

In a database system, sufficient free storage space on the data bank partition is important. In addition to regular database back-ups, it is therefore wise to monitor the free storage space.

When your database partition is full, the Microsoft SQL server sets the status of the database that can no longer grow to “Recovery Pending”. This status means that SQL can’t open the database and can’t lock the database files. This status is comparable to a database in offline modus.

When a database partition is full the status of the SQL server database is set to “Recovery Pending.”

Repair recovery pending SQL server database in 9 steps

In most cases, the following steps will repair the database (in this example: MX_MSCRM):

  1. Increase the available storage space.
  2. Create back-ups of database files *.mdf (Primary Data File), *.ndf (Secondary Data Files, if available) and *.ldf (Log Files).
  3. Set the database to mode “online“:
    ALTER DATABASE MX_MSCRM SET ONLINE
  4. Run CheckDB against the database in question (only warnings):
    DBCC CHECKDB(‘MX_MSCRM’) WITH NO_INFOMSGSIf CheckDB has completed without warning, the database does not need to be repaired.Otherwise proceed to step #5.
  5. Before repair, the database has to be set to single user mode:
    ALTER DATABASE MX_MSCRM SET SINGLE_USER
  6. There are different repair levels. Usually, one begins with “REPAIR_REBUILD“:
    DBCC CHECKDB(‘MX_MSCRM’,REPAIR_REBUILD) If the repair is successful, the database may be set back to multiple user mode (see step #9).
  7. Otherwise, repair level “REPAIR_ALLOW_DATA_LOSS” is next. Please note that (as the name suggests) this may result in a loss of data:
    DBCC CHECKDB(‘MX_MSCRM’,REPAIR_ALLOW_DATA_LOSS) If repair was successful, the database may be set back to multiple user status (see step #9).
  8. Finally, you can try repair via the “EMERGENCY“ mode:
    ALTER DATABASE MX_MSCRM SET EMERGENCY
    ALTER DATABASE MX_MSCRM SET SINGLE_USER
    DBCC CHECKDB (MX_MSCRM,REPAIR_ALLOW_DATA_LOSS) WITH
    NO_INFOMSGS,ALL_ERRORMSGS
  9. Set database to status “Online” and re-activate multiple user mode:
    ALTER DATABASE MX_MSCRM SET ONLINE
    ALTER DATABASE MX_MSCRM SET MULTI_USER If everything worked out, the status “Recovery Pending“ should disappear after refreshing.
6
After performing the steps above, the status “Recovery Pending” disappears.

Here you can learn more about SQL database in recovery pending state.

Answering