One of my colleagues reported an issue with accessing a database as below. Initially, I thought it was something related to disk unavailability or resource related and asked them to verify those parameters. Later they reached to me saying all are intact with disk and memory.

Database ‘dbname’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

While checking in SSMS, it is also noted that the db was showing as “(Not synchronizing / Recovery Pending)” in Object Explorer GUI.

So, the first thing we need to make sure to get this db out of availability group. To do, we need to remove this database from the availability Group with the below code.

Once, we removed the database, we observed the status of the database as “Restoring”. So the next thing was recovering the databases from status “recovering” to “online”. There is another blog post on “How to recover restoring database in SQL Server“. As first step, I tried to get the database by simply restoring the db with recovery option and found the below exception.

Msg 4303, Level 16, State 1, Line 5 The roll forward start point is now at log sequence number (LSN) 67784000000743200001. Additional roll forward past LSN 67785000000710400060 is required to complete the restore sequence. Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE is terminating abnormally.

The above error message is a clear indication that there is some severe issue with I/O that needs a restore from backups (full + logs) for this database. Then we tried to restore the last transaction log file which again ended up with another message as below.

Msg 4305, Level 16, State 1, Line 1 The log in this backup set begins at LSN 67784000000743200001, which is too recent to apply to the database. An earlier log backup that includes LSN 67785000000710400060 can be restored. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.

Since we were not sure of the exact log file, we decided to restore from a most latest valid full backup followed by log backups. Here, is a way to identify backup information of a database.Finally, we were able to get the database online.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!