DB in recovery pending state

Today I modified the password of the service account, then I restart service, found that there is a DB in recovery pending state, view Paul Randal Blog, see the following definition:

RECOVERY PENDING

• The database would be in the if SQL Server knows that recovery needs to be run on the database but something is prev enting recovery from starting. This was different from SUSPECT because there's nothing to say that recovery was going to fail–it just hasn ' t sta RTed yet.
• An example of wasn ' t cleanly shut down (i.e. there were at least one uncommitted transaction acti ve at the time the database is shut down) and the log file have been deleted.

If the recovery hasn't started yet, then this will be a very good state. If there is damage, then, miserably, data loss may occur.

The lesson of Blood and tears: before service Restart, be sure that DB is not running the update operation and use checkpoint to save dirty data.

For the Recovery Pending state, how should I fix it, referring to the "how to resolve the issue of a database, is inRecovery Pending mode"

This error was closely correlated to that forcibly deletion process of the file stream file which I do think no other solution Except waiting to finish recovery mode and either wise it'll end with:

• The most optimistic probability that it'll end up with Online mode ... So it would be fine and no need the further action (Just you had to wait for a longer time if log file is such huge )..
• The most pessimistic probability that it'll end up with suspect mode. So it'll be need to run the below process of DB Repair and bear in mind that data loss might be there;

Stop SQL Server and remove transaction log file of this db then restart again where DB should go with suspect mode .... If so You can run the below query

ALTER DATABASE [db_name] SETSingle_user withno_waitALTER DATABASE [db_name] SETEMERGENCY;DBCCCHECKDB ([db_name], Repair_allow_data_loss)ALTER DATABASE [db_name] SETOnline;ALTER DATABASE [db_name] SETMulti_user withNo_wait

Check the size of the db before using the CHECKDB command repair

Select db_name(mf.database_id) asDatabaseName, Mf.type_desc asFileType, Mf.name asFilelogicname, Mf.physical_name asFilephysicalname, Mf.size asPagescount, Mf.size*8/1024x768   asSIZE_MB, Mf.size*8/1024x768/1024.0  asSIZE_GB fromsys.master_files MFwheremf.database_id= db_id(N'dbname')

At the time of execution, various problems arise:

1,user does not has permission to alter DATABASE ' Office365 ', the database does not exist, or the database is not in a St Ate that allows access checks.

2,database ' Office365 ' cannot is opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Finally, I went to file physical path, could not find the corresponding MDF file, but the log file is present, and the last time the log file was modified is 2 years, it may be abandoned db. and restart the Service account, not a 18GB MDF file delete it, this situation, what is the reason?

Find the following explanations from Database Status:

Recovery Pending State:sql Server has encountered a resource-related error during Recovery. The database is not a damaged, but the files could be missing or system resource limitations could be preventing it from starting. The database is unavailable. Additional action by the user was required to resolve the error and let the recovery process be completed.

Attach database status and its Definition

State

	Definition
ONLINE	Database is available for access. The primary filegroup is online and although the undo phase of recovery may not have been completed.
OFFLINE	Database is unavailable. A database becomes offline by explicit user action and remains offline until additional the user action is taken. For example, the database is taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
RESTORING	One or more files of the primary filegroup is being restored, or one or more secondary files is being restored offline. The database is unavailable.
RECOVERING	Database is being recovered. The recovering process is a transient state; The database would automatically become online if the recovery succeeds. If The recovery fails, the database would become suspect. The database is unavailable.
RECOVERY PENDING	SQL Server has encountered a resource-related error during recovery. The database is not a damaged, but the files could be missing or system resource limitations could be preventing it from starting. The database is unavailable. Additional action by the user was required to resolve the error and let the recovery process be completed.
SUSPECT	At least the primary filegroup are suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user was required to resolve the problem.
EMERGENCY	User have changed the database and set the status to EMERGENCY. The database is in Single-user mode and may repaired or restored. The database is marked READ_ONLY, logging are disabled, and access is limited-members of the sysadmin fixed ser ver role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can is set to the EMERGENCY state. This could permit the system administrator read-only access to the database. The sysadmin fixed server role can set a database to the EMERGENCY state.

Recommended reading:

"Troubleshooting:scom DW Database is in a Suspect state"

"Search Engine q&a #4: Using EMERGENCY mode to access A RECOVERY PENDING or SUSPECT database"

"Corruption:last resorts that people try first ..."

DB in recovery pending state