June 11, 2011 at 10:39 am
Hi Folks, I was in the middle of a long-running insert when the volume which held the primary (so to speak) LDF failed. SQL Server immediately started writing to my secondary (so to speak) LDF. I started seeing a bunch of 18400 and 823 errors in the SQL Server log, indiciating:
The background checkpoint thread has encountered an unrecoverable error. The checkpoint process is terminating so that the thread can clean up its resources.
AND
The operating system returned error 1117 (The requrest could not be performed because of an I/O device error.) to SQL Server during a write at offset 0000000000000 in file [My 'Primary' transaction log].
Though I had my doubts that the transaction would complete (and indeed, it took its sweet time), it finally did. After confirming with IT that the VLUN had actually failed, a new volume was created for me, formatted, etc., and then it was suggested that I reboot the server. I confirmed that no crucial tasks were running and rebooted.
Upon rebooting, my database become completely unavailable. I see the following errors messages in the SQL Server log:
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file [My 'Primary' transaction log]. Diagnose and ...[blah blah] (Error: 17207)
AND
File activation failure. The physical file name [My 'Primary' transaction log] may be incorrect.
AND
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.
OK, well, indeed, the 'primary' log was lost - it is non-recoverable, according to IT. I can't alter the database to have it even point to the 'secondary' log file that I set up. I'm pretty sure that I'm screwed here. Barring a backup, does anyone have any ideas on how I can recover my database?
June 11, 2011 at 10:48 am
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2011 at 5:00 pm
Thanks Gail, much appreciated, as usual.
June 13, 2011 at 12:00 pm
Hi Gail, I followed the instructions on your article and, upon reaching the last step, where I created the new database, stopped the SQL Service, renamed the files, and restarted the SQL Service, I'm unable to run the same DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. For a while now (few hours) it's been telling me that the database is being recovered and that I need to wait until recovery is finished. However, I see no mention of this in the SQL logs or as a process running in dm_exec_requests. Any ideas?
June 13, 2011 at 12:03 pm
Did you miss the step that said to switch the database to emergency mode?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 13, 2011 at 12:09 pm
No, sorry, I missed the step to delete the transaction log from the new database. I'm ok now. Thanks.
June 13, 2011 at 12:14 pm
Excellent.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply