0

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.

Improve this question
5
  • Don't try and detach the database! Just to confirm - they're all still attached, right, just in recovery pending? If they're detached, you have serious problems. Also, can you confirm, have the drives been replaced (with the same logical names/folders for sql files etc. as the originals)?
    – Ian_H
    Commented Jan 24, 2017 at 11:45
  • your system is being recovered.did you see error log for any hints,that says your database has issue
    – TheGameiswar
    Commented Jan 24, 2017 at 12:00
  • @Ian_H The drives have not yet been replaced but my collegues are already in starting.
    – QWE
    Commented Jan 24, 2017 at 12:27
  • @TheGameiswar - Unfortunately, I couldn't see anything of use in there.
    – QWE
    Commented Jan 24, 2017 at 12:29
  • Try .. Alter database yourdb REBUILD LOG ON (NAME=logical_log, FILENAME=’d:\.....\physical_log.ldf’) and then do Dbcc checkdb (yourdb) with no_infomsgs and all_errormsgs
    – Kin Shah
    Commented Jan 24, 2017 at 12:54

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • Thank you Ian_H, I tried both options, but none worked. I will try again after disk is replaced. This server is only used occassionally and we only have ad hoc backups.
    – QWE
    Commented Jan 24, 2017 at 12:34
  • @qwe What error did you get from these? You need the disk replaced to have somewhere to rebuild the log files
    – Ian_H
    Commented Jan 24, 2017 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.