Cannot attach SQL MDF file due to corrupt or missing Log file (Error 1813)

Transaction Log is corrupted. The transaction log ate the 80GB hardisk space which was not normal, so I tried to detach the database, delete the log and rebuild it. Unfortunately the mdf file cannot be re-attach anymore. It prompts the following error:

"Error 1813: Could not open new database '. CREATE DATABASE is aborted.

Device activation error. The physical file name 'C:\ ...\db_name.LDF' may be incorrect."

After a long research for answer I finally arrived in to a solution. Here are the step-by-step procedure I created to resolve the problem:

1. Created a new database with same MDF and LDF name

2. Stop sql server and delete the new MDF file and copy the old MDF file

3. Started SQL Server and verify the status of the database. If the database status is suspect do the following scripts in Query Analyzer:

a. Override updates in Master Table

Use Master

Go

sp_configure 'allow updates', 1

reconfigure with override

Go

b. Get the value of the status column in sysdatabase table

select * from sysdatabases where name = 'db_name'

-- note the value of the status column for later use in # 6

begin tran

c. Set status of the database to suspect (327768)

update sysdatabases set status = 32768 where name ='db_name'

commit tran

d. Rebuild transaction log

DBCC rebuild_log('db_name','db_log_path')

e. Set the database to single user access

sp_dboption '', 'single user', 'true'

DBCC checkdb('db_name')

Go

f. Restore the status of the database in sysdatabases table

begin tran

update sysdatabases set status = previous_value where name = 'db_name'

-- verify one row is updated before committing

commit tran

Go

g. Restore the override property of Master table

sp_configure 'allow updates', 0

reconfigure with override

Go

4. Now open or refresh the enterprise manager you can now see the tables inside the fixed database.

If any problems persist, you can import all tables into temporary database and drop the original database. Create a new database with same name then re-import all the tables from the temporary database

Ref: fryan0911.com