Nov 22, 2016 | Armin Odorfer

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.

5

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

• Increase the available storage space.

• Create back-ups of database files *.mdf (Primary Data File), *.ndf (Secondary Data Files, if available) and *.ldf (Log Files).

• Set the database to mode “online“:
ALTER DATABASE MX_MSCRM SET ONLINE

• 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

• Before repair, the database has to be set to single user mode:
ALTER DATABASE MX_MSCRM SET SINGLE_USER

• 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).

• 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).

• 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

• Set database to status “Online” and re-activate multiple user mode:
ALTER DATABASE MX_MSCRM SET ONLINE
ALTER DATABASE MX_MSCRM SET MULTI_USERIf everything worked out, the status “Recovery Pending“ should disappear after refreshing.>

6

I accept that this site uses cookies for analysis, personalized content and advertisment.