Operating system error 38(Reached the end of the file.) on file "C:\Data\myfile_log.LDF

Hi,

I am facing a problem on a server which has raid 5 solution (3 disks), the raid controller went down 2 of the disks were off in the Bios.

We added the 3 disks to a different server identical in brand and architecture, the raid controller was able to reconfigure the virtual drive H:.

All files were there, we installed sql server 2005 on the new server, but when we tried to attach the database we got the error below:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'myserver'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x00000000af0000 in file 'C:\Data\mylog_log.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.
Operating system error 38(Reached the end of the file.) on file "C:\Data\mylog_log.LDF" during ReadFileHdr.
Could not open new database 'mydb'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 823)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476

BUTTONS:

OK

I tried the following steps but it always failed:

- create a new db with the same name of the lost db;

- put the db in emergency mode;

- stop sql service and replace the mdf file;

- start sql service;

- Run Dbcc checkdb('mydb')

we got the error below:

Msg 945, Level 14, State 2, Line 1

Database 'ism0506' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Any HELP please ?

Thanks,

Tarek Ghazali

Sql Server MVP

Well, the obvious question is where is your most recent backup of the database?

This situation doesn't look good. Losing 2/3 of a RAID 5 set means that you have lost a significant amount of data. Just because the file metada is there doesn't mean that the contents are there or intact. At a minimum, your log file is corrupt.

You can try CREATE DATABASE FOR ATTACH_REBUILD_LOG and see if you can get it going that way.

You WILL lose data, and most probably have inconsistencies due not being able to read the log file to do recovery.

Seriously restore from backup is your best option at this point.

|||

Hi,

The issue is that the client did not backup since 3 weeks (his mistake) and the only solution is to restore from the corrupted files that they have. I tried your solution but it did not work.

This is the error that i got when i ran (CREATE DATABASE FOR ATTACH_REBUILD_LOG ):

The log cannot be rebuilt because the database was not cleanly shut down.

Thanks for your reply,

Tarek Ghazali

SQL Server MVP

|||Try this undocumented stuff provided by Kevin [MS].

==========
1. Back up the .mdf/.ndf files at first!!!

2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

4. Stop and restart SQL server.

5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC rebuild_log('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).

6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go

7. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go
============|||

Satya - none of that works in SQL Server 2005 (e.g. I removed the DBCC REBUILD_LOG command).

Tarek - you should call Product Support to help with recovering this.

Thanks