“Database ‘x’ cannot be opened due to inaccessible files or insufficient memory or disk space” and “Initializing/Recovery Pending”

 

There may be several reasons for this error. A friend of mine said that when he received this error, the database files were deleted. They solved the problem by returning from Backup.

In some cases (uncontrolled server restart, etc.), the Always On Avilability Group Secondary database falls into Initializing/Recovery Pending mode.

In this case, sometimes, getting the Secondary database to Suspend mode through the Availability Group and then Resume resolves the problem.

Well, that didn’t solve the problem.

Getting the Secondary Database to Suspend Mode via script:

Getting the Secondary Database to Suspend Mode via SSMS:

Right Click on database on the Availability Databases section, then click Suspend Data Movement.

Because the problem is not solved in this way, right-click on the secondary database in the Availability Databases tab and click “Remove Secondary Database”.

This process can keep you waiting a bit. Because, the database falls to Initializing/Recovery Pending because of a problem in the structure of the secondary database. And it needs a time to recover.

If you look at SQL Server Log, you can monitor the process. This period will vary according to the size of the database. I waited 7 hours for a database of 6 TB. There is no interruption in the application because it is a secondary database.

After the recover process is complete, you will see that the problematic database has an exclamation mark on it. Right click on this database and click “Join to Availability Group”. After a while, you will see that the status of the secondary database is synchronized again and the problem is corrected.

If you took a log backup from the primary database after this problem, you may encounter an error like below during the join operation.

The mirror database,”xxx”, has insufficient transaction log data to preserver the log backup chain of the principal database. This may happen if a log backup principal database has not been taken or has not been restored on the mirror database.

You must restore log backup that taken from the primary database, to the secondary database and try the same operation again. If you can’t find the log backup, or if you took the  backup with another backup application,  take a differential backup, then take a log backup from the primary database, and restore those backups to the secondary database in norecovery mode respectively.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *