Sql-server – Drive with ldf files failed and some databases left in recovery pending status

corruptionsql serversql-server-2012

We are keeping mdf and ldf files on seperate drives of a physical machine. One of the disks forming the drive for ldf files failed. Some of the databases that had their ldfs on that drive are now in "Recovery pending" status.

It is not a production environment so some data loss is acceptable but losing entire databases is not. Restoring from backups is not a viable option at this time.

I tried to:

1) Detach databases, but I got following error:

File activation failure. The physical file name "D:\LDFfiles\DatabaseName.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

Msg 5181, Level 16, State 5, Line 1 Could not restart database
"DatabaseName". Reverting to the previous status.

Msg 5069, Level 16,
State 1, Line 1 ALTER DATABASE statement failed.

Msg 823, Level 24,
State 2, Line 1 The operating system returned error 2(The system
cannot find the file specified.) to SQL Server during a read at offset
0000000000000000 in file 'D:\LDFfiles\DatabaseName.ldf'. Additional
messages in the SQL Server error log and system event log may provide
more detail. This is a severe system-level error condition that
threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error
can be caused by many factors; for more information, see SQL Server
Books Online.

2) Bring them online, but I got following error:

enter image description here

Could you please advise what are my options to recover the database? Best to my knowledge mdf file is healthy and can be reused with rebuild log query. The main problem is that it is currently attached. Any advice will be welcome.

Best Answer

You have two options (if restoring from backup isn't an option), and both of them require the database to be attached - your problems are even more serious if you database is no attached (that's the reason why SQL Server prevents you from detaching. Lucky you - older versions didn't look after you like this.

You can either run CHECKDB and allow data loss, or you can ALTER DATABASE REBUILD LOG - but REBUILD LOG is iffy and might not work if you database is in pending recovery anyway.

You need to run CHECKDB. The syntax is as follows:

ALTER DATABASE [fluffedDB] SET EMERGENCY;   
ALTER DATABASE [fluffedDB] SET SINGLE_USER;  

DBCC CHECKDB ([fluffedDB], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;

Warning: You will get data loss, and none of this is guaranteed. Read this SQL Skills post by Paul Randal about EMERGENCY-mode repair

Assumption: The logical disk where the log file is has been repaired/restored.

Query: Why are restoring from backups not viable? In any environment where you even remotely care about the data, always have a backup, as a restore from backup is always usually the easiest way to solve problems like this.

Related Question