October 3, 2011 at 10:18 am
I had a long running transaction last week, and had a feeling that my transaction log was going to run out of space on its volume before the transaction completed. So, I created a secondary transaction log on a SAN volume which my network admin set up for me, with the idea of removing it as soon as the transaction was done. That very frickin' night, the underlying disks to the SAN volume suffered a catastrophic failure. Unbeannounced to me, this volume was also set up as a RAID0 volume, so no redundancy. SQL Server had already begun to write to the secondary log when it failed, so now, the database will not start up.
Short of restoring from a week-old backup, does anyone have any brilliant ideas on recovering a DB in such a state?
October 3, 2011 at 10:27 am
Post the SQL error log, from the time of the failure with any messages relating to that DB.
What's the exact state of the database (state_desc from sys.databases for that DB)
A weekly backup with no transaction log backups? That's just asking for data loss.
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
October 3, 2011 at 10:40 am
Yes, yes, shame on me about the backup schedule. Let's please withhold judgment for now, ok?
State of DB (OHM) is "RECOVERY_PENDING"
Log -
09/29/2011 23:03:57,spid51,Unknown,During undoing of a logged operation in database 'OHM'<c/> an error occurred at log record ID (218715:6016:227). Typically<c/> the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup<c/> or repair the database.
09/29/2011 23:03:57,spid51,Unknown,Error: 3314<c/> Severity: 21<c/> State: 5.
09/29/2011 23:03:57,spid51,Unknown,The log for database 'OHM' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
09/29/2011 23:03:57,spid51,Unknown,Error: 9001<c/> Severity: 21<c/> State: 1.
09/29/2011 23:03:57,spid51,Unknown,During undoing of a logged operation in database 'OHM'<c/> an error occurred at log record ID (232766:8896:463). Typically<c/> the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup<c/> or repair the database.
09/29/2011 23:03:57,spid51,Unknown,Error: 3314<c/> Severity: 21<c/> State: 4.
09/29/2011 23:03:57,spid51,Unknown,The log for database 'OHM' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
09/29/2011 23:03:57,spid51,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.
09/29/2011 23:03:57,spid51,Unknown,Database OHM was shutdown due to error 5159 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
09/29/2011 23:03:57,spid51,Unknown,During undoing of a logged operation in database 'OHM'<c/> an error occurred at log record ID (231584:9388:504). Typically<c/> the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup<c/> or repair the database.
09/29/2011 23:03:57,spid51,Unknown,Error: 3314<c/> Severity: 21<c/> State: 4.
09/29/2011 23:03:57,spid51,Unknown,Operating system error 23(Data error (cyclic redundancy check).) on file "V:\OHM_log2.ldf" during CheckLogBlockReadComplete.
09/29/2011 23:03:57,spid51,Unknown,Error: 5159<c/> Severity: 24<c/> State: 11.
09/29/2011 23:01:30,spid12s,Unknown,SQL Server has encountered 4 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [V:\OHM_log2.ldf] in database [OHM] (5). The OS file handle is 0x0000000000001A20. The offset of the latest long I/O is: 0x000051ee915800
09/29/2011 22:57:58,spid51,Unknown,A read of the file 'V:\OHM_log2.ldf' at offset 0x000051f026e000 succeeded after failing 1 time(s) with error: 23(Data error (cyclic redundancy check).). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.
09/29/2011 22:56:19,spid12s,Unknown,SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [V:\OHM_log2.ldf] in database [OHM] (5). The OS file handle is 0x0000000000001A20. The offset of the latest long I/O is: 0x000051f026e000
09/29/2011 22:46:00,spid51,Unknown,A read of the file 'V:\OHM_log2.ldf' at offset 0x000052b1914000 succeeded after failing 2 time(s) with error: 23(Data error (cyclic redundancy check).). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.
09/29/2011 22:42:03,spid12s,Unknown,SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [V:\OHM_log2.ldf] in database [OHM] (5). The OS file handle is 0x0000000000001A20. The offset of the latest long I/O is: 0x000052b1914000
09/29/2011 22:39:25,spid51,Unknown,A read of the file 'V:\OHM_log2.ldf' at offset 0x000052e3266000 succeeded after failing 2 time(s) with error: 23(Data error (cyclic redundancy check).). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information<c/> see SQL Server Books Online.
09/29/2011 22:36:41,spid12s,Unknown,SQL Server has encountered 28 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [V:\OHM_log2.ldf] in database [OHM] (5). The OS file handle is 0x0000000000001A20. The offset of the latest long I/O is: 0x000052e3266000
October 3, 2011 at 10:45 am
It should be repairable, but you will likely lose data.
http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
If the process gives any errors stop there and post the errors.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply