April 22, 2010 at 3:07 pm
We had a disk fail in an array on one of our machines. The sys admins managed to get the volume that housed the .mdf files back on line, however after starting SQL they are not functional.
I receive a "Database 'wah' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL errorlog for details." when attempting to browse in SSMS 2005 on the server with the failure.
I've also attempted various recovery techniques outlined by Mr. Paul Randal on his fantastic blog (http://www.sqlskills.com/blogs/paul/post/CHECKDB-From-Every-Angle-EMERGENCY-mode-repair-the-very-very-last-resort.aspx). I managed to attach via his hack method set it to Emergency and single-user mode, however; I cannot execute a CHECKDB. I receive the following message when attempting to do so:
"Msg 922, Level 14, State 1, Line 1
Database 'wah' is being recovered. Waiting until recovery is finished."
When reviewing the error logs I can see this for the database in question on startup:
"SQL Server detected a logical consistency-based I/O error: restore pending. It occurred during a read of page (1:0) in database ID 253 at offset 0000000000000000 in file 'E:\MSSQL\DATA\wah.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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."
The database was not busy at the time of failure so there should have been nothing pending in the transaction logs. This seems to be an issue in the boot page of the .mdf. Is this type of corruption a lost cause or is there something else I can attempt? Thanks in advance.
April 27, 2010 at 8:00 am
did you attach it without the log file after restarting SQL and switching out the data files?
i would also make sure that your file paths are correct and just like in production. i usually do this on a vmware instance
April 27, 2010 at 8:11 am
That's the file header page for the mdf - the first page that SQL Server looks at and one of two pages that corruption cannot be tolerated on nor repaired (the other being the boot page on (1:9)). If you don't have any backups then you've lost this database I'm afraid.
A little more info: Search Engine Q&A #21: File header pages, and file header corruption
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 27, 2010 at 8:26 am
Thank you both. Paul, your blog has been most helpful and I threw the kitchen sink at this, so to speak. I began to suspect exactly what you mentioned above, but was just looking to hear it from someone with more experience. Unfortunately, I was not able to repair the corruption. Fortunately, I had the scripts to recreate the database handy and then regenerated the data.
Rule #1: Ensure there are backups...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply